1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import textwrap
  20import typing as t
  21from collections import deque
  22from copy import deepcopy
  23from decimal import Decimal
  24from enum import auto
  25from functools import reduce
  26
  27from sqlglot.errors import ErrorLevel, ParseError
  28from sqlglot.helper import (
  29    AutoName,
  30    camel_to_snake_case,
  31    ensure_collection,
  32    ensure_list,
  33    seq_get,
  34    subclasses,
  35)
  36from sqlglot.tokens import Token, TokenError
  37
  38if t.TYPE_CHECKING:
  39    from sqlglot._typing import E, Lit
  40    from sqlglot.dialects.dialect import DialectType
  41
  42    Q = t.TypeVar("Q", bound="Query")
  43    S = t.TypeVar("S", bound="SetOperation")
  44
  45
  46class _Expression(type):
  47    def __new__(cls, clsname, bases, attrs):
  48        klass = super().__new__(cls, clsname, bases, attrs)
  49
  50        # When an Expression class is created, its key is automatically set to be
  51        # the lowercase version of the class' name.
  52        klass.key = clsname.lower()
  53
  54        # This is so that docstrings are not inherited in pdoc
  55        klass.__doc__ = klass.__doc__ or ""
  56
  57        return klass
  58
  59
  60SQLGLOT_META = "sqlglot.meta"
  61TABLE_PARTS = ("this", "db", "catalog")
  62COLUMN_PARTS = ("this", "table", "db", "catalog")
  63
  64
  65class Expression(metaclass=_Expression):
  66    """
  67    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  68    context, such as its child expressions, their names (arg keys), and whether a given child expression
  69    is optional or not.
  70
  71    Attributes:
  72        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  73            and representing expressions as strings.
  74        arg_types: determines the arguments (child nodes) supported by an expression. It maps
  75            arg keys to booleans that indicate whether the corresponding args are optional.
  76        parent: a reference to the parent expression (or None, in case of root expressions).
  77        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  78            uses to refer to it.
  79        index: the index of an expression if it is inside of a list argument in its parent.
  80        comments: a list of comments that are associated with a given expression. This is used in
  81            order to preserve comments when transpiling SQL code.
  82        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  83            optimizer, in order to enable some transformations that require type information.
  84        meta: a dictionary that can be used to store useful metadata for a given expression.
  85
  86    Example:
  87        >>> class Foo(Expression):
  88        ...     arg_types = {"this": True, "expression": False}
  89
  90        The above definition informs us that Foo is an Expression that requires an argument called
  91        "this" and may also optionally receive an argument called "expression".
  92
  93    Args:
  94        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  95    """
  96
  97    key = "expression"
  98    arg_types = {"this": True}
  99    __slots__ = ("args", "parent", "arg_key", "index", "comments", "_type", "_meta", "_hash")
 100
 101    def __init__(self, **args: t.Any):
 102        self.args: t.Dict[str, t.Any] = args
 103        self.parent: t.Optional[Expression] = None
 104        self.arg_key: t.Optional[str] = None
 105        self.index: t.Optional[int] = None
 106        self.comments: t.Optional[t.List[str]] = None
 107        self._type: t.Optional[DataType] = None
 108        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 109        self._hash: t.Optional[int] = None
 110
 111        for arg_key, value in self.args.items():
 112            self._set_parent(arg_key, value)
 113
 114    def __eq__(self, other) -> bool:
 115        return type(self) is type(other) and hash(self) == hash(other)
 116
 117    @property
 118    def hashable_args(self) -> t.Any:
 119        return frozenset(
 120            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 121            for k, v in self.args.items()
 122            if not (v is None or v is False or (type(v) is list and not v))
 123        )
 124
 125    def __hash__(self) -> int:
 126        if self._hash is not None:
 127            return self._hash
 128
 129        return hash((self.__class__, self.hashable_args))
 130
 131    @property
 132    def this(self) -> t.Any:
 133        """
 134        Retrieves the argument with key "this".
 135        """
 136        return self.args.get("this")
 137
 138    @property
 139    def expression(self) -> t.Any:
 140        """
 141        Retrieves the argument with key "expression".
 142        """
 143        return self.args.get("expression")
 144
 145    @property
 146    def expressions(self) -> t.List[t.Any]:
 147        """
 148        Retrieves the argument with key "expressions".
 149        """
 150        return self.args.get("expressions") or []
 151
 152    def text(self, key) -> str:
 153        """
 154        Returns a textual representation of the argument corresponding to "key". This can only be used
 155        for args that are strings or leaf Expression instances, such as identifiers and literals.
 156        """
 157        field = self.args.get(key)
 158        if isinstance(field, str):
 159            return field
 160        if isinstance(field, (Identifier, Literal, Var)):
 161            return field.this
 162        if isinstance(field, (Star, Null)):
 163            return field.name
 164        return ""
 165
 166    @property
 167    def is_string(self) -> bool:
 168        """
 169        Checks whether a Literal expression is a string.
 170        """
 171        return isinstance(self, Literal) and self.args["is_string"]
 172
 173    @property
 174    def is_number(self) -> bool:
 175        """
 176        Checks whether a Literal expression is a number.
 177        """
 178        return (isinstance(self, Literal) and not self.args["is_string"]) or (
 179            isinstance(self, Neg) and self.this.is_number
 180        )
 181
 182    def to_py(self) -> t.Any:
 183        """
 184        Returns a Python object equivalent of the SQL node.
 185        """
 186        raise ValueError(f"{self} cannot be converted to a Python object.")
 187
 188    @property
 189    def is_int(self) -> bool:
 190        """
 191        Checks whether an expression is an integer.
 192        """
 193        return self.is_number and isinstance(self.to_py(), int)
 194
 195    @property
 196    def is_star(self) -> bool:
 197        """Checks whether an expression is a star."""
 198        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 199
 200    @property
 201    def alias(self) -> str:
 202        """
 203        Returns the alias of the expression, or an empty string if it's not aliased.
 204        """
 205        if isinstance(self.args.get("alias"), TableAlias):
 206            return self.args["alias"].name
 207        return self.text("alias")
 208
 209    @property
 210    def alias_column_names(self) -> t.List[str]:
 211        table_alias = self.args.get("alias")
 212        if not table_alias:
 213            return []
 214        return [c.name for c in table_alias.args.get("columns") or []]
 215
 216    @property
 217    def name(self) -> str:
 218        return self.text("this")
 219
 220    @property
 221    def alias_or_name(self) -> str:
 222        return self.alias or self.name
 223
 224    @property
 225    def output_name(self) -> str:
 226        """
 227        Name of the output column if this expression is a selection.
 228
 229        If the Expression has no output name, an empty string is returned.
 230
 231        Example:
 232            >>> from sqlglot import parse_one
 233            >>> parse_one("SELECT a").expressions[0].output_name
 234            'a'
 235            >>> parse_one("SELECT b AS c").expressions[0].output_name
 236            'c'
 237            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 238            ''
 239        """
 240        return ""
 241
 242    @property
 243    def type(self) -> t.Optional[DataType]:
 244        return self._type
 245
 246    @type.setter
 247    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 248        if dtype and not isinstance(dtype, DataType):
 249            dtype = DataType.build(dtype)
 250        self._type = dtype  # type: ignore
 251
 252    def is_type(self, *dtypes) -> bool:
 253        return self.type is not None and self.type.is_type(*dtypes)
 254
 255    def is_leaf(self) -> bool:
 256        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 257
 258    @property
 259    def meta(self) -> t.Dict[str, t.Any]:
 260        if self._meta is None:
 261            self._meta = {}
 262        return self._meta
 263
 264    def __deepcopy__(self, memo):
 265        root = self.__class__()
 266        stack = [(self, root)]
 267
 268        while stack:
 269            node, copy = stack.pop()
 270
 271            if node.comments is not None:
 272                copy.comments = deepcopy(node.comments)
 273            if node._type is not None:
 274                copy._type = deepcopy(node._type)
 275            if node._meta is not None:
 276                copy._meta = deepcopy(node._meta)
 277            if node._hash is not None:
 278                copy._hash = node._hash
 279
 280            for k, vs in node.args.items():
 281                if hasattr(vs, "parent"):
 282                    stack.append((vs, vs.__class__()))
 283                    copy.set(k, stack[-1][-1])
 284                elif type(vs) is list:
 285                    copy.args[k] = []
 286
 287                    for v in vs:
 288                        if hasattr(v, "parent"):
 289                            stack.append((v, v.__class__()))
 290                            copy.append(k, stack[-1][-1])
 291                        else:
 292                            copy.append(k, v)
 293                else:
 294                    copy.args[k] = vs
 295
 296        return root
 297
 298    def copy(self):
 299        """
 300        Returns a deep copy of the expression.
 301        """
 302        return deepcopy(self)
 303
 304    def add_comments(self, comments: t.Optional[t.List[str]] = None) -> None:
 305        if self.comments is None:
 306            self.comments = []
 307
 308        if comments:
 309            for comment in comments:
 310                _, *meta = comment.split(SQLGLOT_META)
 311                if meta:
 312                    for kv in "".join(meta).split(","):
 313                        k, *v = kv.split("=")
 314                        value = v[0].strip() if v else True
 315                        self.meta[k.strip()] = value
 316                self.comments.append(comment)
 317
 318    def pop_comments(self) -> t.List[str]:
 319        comments = self.comments or []
 320        self.comments = None
 321        return comments
 322
 323    def append(self, arg_key: str, value: t.Any) -> None:
 324        """
 325        Appends value to arg_key if it's a list or sets it as a new list.
 326
 327        Args:
 328            arg_key (str): name of the list expression arg
 329            value (Any): value to append to the list
 330        """
 331        if type(self.args.get(arg_key)) is not list:
 332            self.args[arg_key] = []
 333        self._set_parent(arg_key, value)
 334        values = self.args[arg_key]
 335        if hasattr(value, "parent"):
 336            value.index = len(values)
 337        values.append(value)
 338
 339    def set(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 340        """
 341        Sets arg_key to value.
 342
 343        Args:
 344            arg_key: name of the expression arg.
 345            value: value to set the arg to.
 346            index: if the arg is a list, this specifies what position to add the value in it.
 347        """
 348        if index is not None:
 349            expressions = self.args.get(arg_key) or []
 350
 351            if seq_get(expressions, index) is None:
 352                return
 353            if value is None:
 354                expressions.pop(index)
 355                for v in expressions[index:]:
 356                    v.index = v.index - 1
 357                return
 358
 359            if isinstance(value, list):
 360                expressions.pop(index)
 361                expressions[index:index] = value
 362            else:
 363                expressions[index] = value
 364
 365            value = expressions
 366        elif value is None:
 367            self.args.pop(arg_key, None)
 368            return
 369
 370        self.args[arg_key] = value
 371        self._set_parent(arg_key, value, index)
 372
 373    def _set_parent(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 374        if hasattr(value, "parent"):
 375            value.parent = self
 376            value.arg_key = arg_key
 377            value.index = index
 378        elif type(value) is list:
 379            for index, v in enumerate(value):
 380                if hasattr(v, "parent"):
 381                    v.parent = self
 382                    v.arg_key = arg_key
 383                    v.index = index
 384
 385    @property
 386    def depth(self) -> int:
 387        """
 388        Returns the depth of this tree.
 389        """
 390        if self.parent:
 391            return self.parent.depth + 1
 392        return 0
 393
 394    def iter_expressions(self, reverse: bool = False) -> t.Iterator[Expression]:
 395        """Yields the key and expression for all arguments, exploding list args."""
 396        # remove tuple when python 3.7 is deprecated
 397        for vs in reversed(tuple(self.args.values())) if reverse else self.args.values():
 398            if type(vs) is list:
 399                for v in reversed(vs) if reverse else vs:
 400                    if hasattr(v, "parent"):
 401                        yield v
 402            else:
 403                if hasattr(vs, "parent"):
 404                    yield vs
 405
 406    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 407        """
 408        Returns the first node in this tree which matches at least one of
 409        the specified types.
 410
 411        Args:
 412            expression_types: the expression type(s) to match.
 413            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 414
 415        Returns:
 416            The node which matches the criteria or None if no such node was found.
 417        """
 418        return next(self.find_all(*expression_types, bfs=bfs), None)
 419
 420    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 421        """
 422        Returns a generator object which visits all nodes in this tree and only
 423        yields those that match at least one of the specified expression types.
 424
 425        Args:
 426            expression_types: the expression type(s) to match.
 427            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 428
 429        Returns:
 430            The generator object.
 431        """
 432        for expression in self.walk(bfs=bfs):
 433            if isinstance(expression, expression_types):
 434                yield expression
 435
 436    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 437        """
 438        Returns a nearest parent matching expression_types.
 439
 440        Args:
 441            expression_types: the expression type(s) to match.
 442
 443        Returns:
 444            The parent node.
 445        """
 446        ancestor = self.parent
 447        while ancestor and not isinstance(ancestor, expression_types):
 448            ancestor = ancestor.parent
 449        return ancestor  # type: ignore
 450
 451    @property
 452    def parent_select(self) -> t.Optional[Select]:
 453        """
 454        Returns the parent select statement.
 455        """
 456        return self.find_ancestor(Select)
 457
 458    @property
 459    def same_parent(self) -> bool:
 460        """Returns if the parent is the same class as itself."""
 461        return type(self.parent) is self.__class__
 462
 463    def root(self) -> Expression:
 464        """
 465        Returns the root expression of this tree.
 466        """
 467        expression = self
 468        while expression.parent:
 469            expression = expression.parent
 470        return expression
 471
 472    def walk(
 473        self, bfs: bool = True, prune: t.Optional[t.Callable[[Expression], bool]] = None
 474    ) -> t.Iterator[Expression]:
 475        """
 476        Returns a generator object which visits all nodes in this tree.
 477
 478        Args:
 479            bfs: if set to True the BFS traversal order will be applied,
 480                otherwise the DFS traversal will be used instead.
 481            prune: callable that returns True if the generator should stop traversing
 482                this branch of the tree.
 483
 484        Returns:
 485            the generator object.
 486        """
 487        if bfs:
 488            yield from self.bfs(prune=prune)
 489        else:
 490            yield from self.dfs(prune=prune)
 491
 492    def dfs(
 493        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 494    ) -> t.Iterator[Expression]:
 495        """
 496        Returns a generator object which visits all nodes in this tree in
 497        the DFS (Depth-first) order.
 498
 499        Returns:
 500            The generator object.
 501        """
 502        stack = [self]
 503
 504        while stack:
 505            node = stack.pop()
 506
 507            yield node
 508
 509            if prune and prune(node):
 510                continue
 511
 512            for v in node.iter_expressions(reverse=True):
 513                stack.append(v)
 514
 515    def bfs(
 516        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 517    ) -> t.Iterator[Expression]:
 518        """
 519        Returns a generator object which visits all nodes in this tree in
 520        the BFS (Breadth-first) order.
 521
 522        Returns:
 523            The generator object.
 524        """
 525        queue = deque([self])
 526
 527        while queue:
 528            node = queue.popleft()
 529
 530            yield node
 531
 532            if prune and prune(node):
 533                continue
 534
 535            for v in node.iter_expressions():
 536                queue.append(v)
 537
 538    def unnest(self):
 539        """
 540        Returns the first non parenthesis child or self.
 541        """
 542        expression = self
 543        while type(expression) is Paren:
 544            expression = expression.this
 545        return expression
 546
 547    def unalias(self):
 548        """
 549        Returns the inner expression if this is an Alias.
 550        """
 551        if isinstance(self, Alias):
 552            return self.this
 553        return self
 554
 555    def unnest_operands(self):
 556        """
 557        Returns unnested operands as a tuple.
 558        """
 559        return tuple(arg.unnest() for arg in self.iter_expressions())
 560
 561    def flatten(self, unnest=True):
 562        """
 563        Returns a generator which yields child nodes whose parents are the same class.
 564
 565        A AND B AND C -> [A, B, C]
 566        """
 567        for node in self.dfs(prune=lambda n: n.parent and type(n) is not self.__class__):
 568            if type(node) is not self.__class__:
 569                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 570
 571    def __str__(self) -> str:
 572        return self.sql()
 573
 574    def __repr__(self) -> str:
 575        return _to_s(self)
 576
 577    def to_s(self) -> str:
 578        """
 579        Same as __repr__, but includes additional information which can be useful
 580        for debugging, like empty or missing args and the AST nodes' object IDs.
 581        """
 582        return _to_s(self, verbose=True)
 583
 584    def sql(self, dialect: DialectType = None, **opts) -> str:
 585        """
 586        Returns SQL string representation of this tree.
 587
 588        Args:
 589            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 590            opts: other `sqlglot.generator.Generator` options.
 591
 592        Returns:
 593            The SQL string.
 594        """
 595        from sqlglot.dialects import Dialect
 596
 597        return Dialect.get_or_raise(dialect).generate(self, **opts)
 598
 599    def transform(self, fun: t.Callable, *args: t.Any, copy: bool = True, **kwargs) -> Expression:
 600        """
 601        Visits all tree nodes (excluding already transformed ones)
 602        and applies the given transformation function to each node.
 603
 604        Args:
 605            fun: a function which takes a node as an argument and returns a
 606                new transformed node or the same node without modifications. If the function
 607                returns None, then the corresponding node will be removed from the syntax tree.
 608            copy: if set to True a new tree instance is constructed, otherwise the tree is
 609                modified in place.
 610
 611        Returns:
 612            The transformed tree.
 613        """
 614        root = None
 615        new_node = None
 616
 617        for node in (self.copy() if copy else self).dfs(prune=lambda n: n is not new_node):
 618            parent, arg_key, index = node.parent, node.arg_key, node.index
 619            new_node = fun(node, *args, **kwargs)
 620
 621            if not root:
 622                root = new_node
 623            elif new_node is not node:
 624                parent.set(arg_key, new_node, index)
 625
 626        assert root
 627        return root.assert_is(Expression)
 628
 629    @t.overload
 630    def replace(self, expression: E) -> E: ...
 631
 632    @t.overload
 633    def replace(self, expression: None) -> None: ...
 634
 635    def replace(self, expression):
 636        """
 637        Swap out this expression with a new expression.
 638
 639        For example::
 640
 641            >>> tree = Select().select("x").from_("tbl")
 642            >>> tree.find(Column).replace(column("y"))
 643            Column(
 644              this=Identifier(this=y, quoted=False))
 645            >>> tree.sql()
 646            'SELECT y FROM tbl'
 647
 648        Args:
 649            expression: new node
 650
 651        Returns:
 652            The new expression or expressions.
 653        """
 654        parent = self.parent
 655
 656        if not parent or parent is expression:
 657            return expression
 658
 659        key = self.arg_key
 660        value = parent.args.get(key)
 661
 662        if type(expression) is list and isinstance(value, Expression):
 663            # We are trying to replace an Expression with a list, so it's assumed that
 664            # the intention was to really replace the parent of this expression.
 665            value.parent.replace(expression)
 666        else:
 667            parent.set(key, expression, self.index)
 668
 669        if expression is not self:
 670            self.parent = None
 671            self.arg_key = None
 672            self.index = None
 673
 674        return expression
 675
 676    def pop(self: E) -> E:
 677        """
 678        Remove this expression from its AST.
 679
 680        Returns:
 681            The popped expression.
 682        """
 683        self.replace(None)
 684        return self
 685
 686    def assert_is(self, type_: t.Type[E]) -> E:
 687        """
 688        Assert that this `Expression` is an instance of `type_`.
 689
 690        If it is NOT an instance of `type_`, this raises an assertion error.
 691        Otherwise, this returns this expression.
 692
 693        Examples:
 694            This is useful for type security in chained expressions:
 695
 696            >>> import sqlglot
 697            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 698            'SELECT x, z FROM y'
 699        """
 700        if not isinstance(self, type_):
 701            raise AssertionError(f"{self} is not {type_}.")
 702        return self
 703
 704    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 705        """
 706        Checks if this expression is valid (e.g. all mandatory args are set).
 707
 708        Args:
 709            args: a sequence of values that were used to instantiate a Func expression. This is used
 710                to check that the provided arguments don't exceed the function argument limit.
 711
 712        Returns:
 713            A list of error messages for all possible errors that were found.
 714        """
 715        errors: t.List[str] = []
 716
 717        for k in self.args:
 718            if k not in self.arg_types:
 719                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 720        for k, mandatory in self.arg_types.items():
 721            v = self.args.get(k)
 722            if mandatory and (v is None or (isinstance(v, list) and not v)):
 723                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 724
 725        if (
 726            args
 727            and isinstance(self, Func)
 728            and len(args) > len(self.arg_types)
 729            and not self.is_var_len_args
 730        ):
 731            errors.append(
 732                f"The number of provided arguments ({len(args)}) is greater than "
 733                f"the maximum number of supported arguments ({len(self.arg_types)})"
 734            )
 735
 736        return errors
 737
 738    def dump(self):
 739        """
 740        Dump this Expression to a JSON-serializable dict.
 741        """
 742        from sqlglot.serde import dump
 743
 744        return dump(self)
 745
 746    @classmethod
 747    def load(cls, obj):
 748        """
 749        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 750        """
 751        from sqlglot.serde import load
 752
 753        return load(obj)
 754
 755    def and_(
 756        self,
 757        *expressions: t.Optional[ExpOrStr],
 758        dialect: DialectType = None,
 759        copy: bool = True,
 760        **opts,
 761    ) -> Condition:
 762        """
 763        AND this condition with one or multiple expressions.
 764
 765        Example:
 766            >>> condition("x=1").and_("y=1").sql()
 767            'x = 1 AND y = 1'
 768
 769        Args:
 770            *expressions: the SQL code strings to parse.
 771                If an `Expression` instance is passed, it will be used as-is.
 772            dialect: the dialect used to parse the input expression.
 773            copy: whether to copy the involved expressions (only applies to Expressions).
 774            opts: other options to use to parse the input expressions.
 775
 776        Returns:
 777            The new And condition.
 778        """
 779        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 780
 781    def or_(
 782        self,
 783        *expressions: t.Optional[ExpOrStr],
 784        dialect: DialectType = None,
 785        copy: bool = True,
 786        **opts,
 787    ) -> Condition:
 788        """
 789        OR this condition with one or multiple expressions.
 790
 791        Example:
 792            >>> condition("x=1").or_("y=1").sql()
 793            'x = 1 OR y = 1'
 794
 795        Args:
 796            *expressions: the SQL code strings to parse.
 797                If an `Expression` instance is passed, it will be used as-is.
 798            dialect: the dialect used to parse the input expression.
 799            copy: whether to copy the involved expressions (only applies to Expressions).
 800            opts: other options to use to parse the input expressions.
 801
 802        Returns:
 803            The new Or condition.
 804        """
 805        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 806
 807    def not_(self, copy: bool = True):
 808        """
 809        Wrap this condition with NOT.
 810
 811        Example:
 812            >>> condition("x=1").not_().sql()
 813            'NOT x = 1'
 814
 815        Args:
 816            copy: whether to copy this object.
 817
 818        Returns:
 819            The new Not instance.
 820        """
 821        return not_(self, copy=copy)
 822
 823    def as_(
 824        self,
 825        alias: str | Identifier,
 826        quoted: t.Optional[bool] = None,
 827        dialect: DialectType = None,
 828        copy: bool = True,
 829        **opts,
 830    ) -> Alias:
 831        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 832
 833    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 834        this = self.copy()
 835        other = convert(other, copy=True)
 836        if not isinstance(this, klass) and not isinstance(other, klass):
 837            this = _wrap(this, Binary)
 838            other = _wrap(other, Binary)
 839        if reverse:
 840            return klass(this=other, expression=this)
 841        return klass(this=this, expression=other)
 842
 843    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 844        return Bracket(
 845            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 846        )
 847
 848    def __iter__(self) -> t.Iterator:
 849        if "expressions" in self.arg_types:
 850            return iter(self.args.get("expressions") or [])
 851        # We define this because __getitem__ converts Expression into an iterable, which is
 852        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 853        # See: https://peps.python.org/pep-0234/
 854        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 855
 856    def isin(
 857        self,
 858        *expressions: t.Any,
 859        query: t.Optional[ExpOrStr] = None,
 860        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 861        copy: bool = True,
 862        **opts,
 863    ) -> In:
 864        subquery = maybe_parse(query, copy=copy, **opts) if query else None
 865        if subquery and not isinstance(subquery, Subquery):
 866            subquery = subquery.subquery(copy=False)
 867
 868        return In(
 869            this=maybe_copy(self, copy),
 870            expressions=[convert(e, copy=copy) for e in expressions],
 871            query=subquery,
 872            unnest=(
 873                Unnest(
 874                    expressions=[
 875                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
 876                        for e in ensure_list(unnest)
 877                    ]
 878                )
 879                if unnest
 880                else None
 881            ),
 882        )
 883
 884    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 885        return Between(
 886            this=maybe_copy(self, copy),
 887            low=convert(low, copy=copy, **opts),
 888            high=convert(high, copy=copy, **opts),
 889        )
 890
 891    def is_(self, other: ExpOrStr) -> Is:
 892        return self._binop(Is, other)
 893
 894    def like(self, other: ExpOrStr) -> Like:
 895        return self._binop(Like, other)
 896
 897    def ilike(self, other: ExpOrStr) -> ILike:
 898        return self._binop(ILike, other)
 899
 900    def eq(self, other: t.Any) -> EQ:
 901        return self._binop(EQ, other)
 902
 903    def neq(self, other: t.Any) -> NEQ:
 904        return self._binop(NEQ, other)
 905
 906    def rlike(self, other: ExpOrStr) -> RegexpLike:
 907        return self._binop(RegexpLike, other)
 908
 909    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 910        div = self._binop(Div, other)
 911        div.args["typed"] = typed
 912        div.args["safe"] = safe
 913        return div
 914
 915    def asc(self, nulls_first: bool = True) -> Ordered:
 916        return Ordered(this=self.copy(), nulls_first=nulls_first)
 917
 918    def desc(self, nulls_first: bool = False) -> Ordered:
 919        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 920
 921    def __lt__(self, other: t.Any) -> LT:
 922        return self._binop(LT, other)
 923
 924    def __le__(self, other: t.Any) -> LTE:
 925        return self._binop(LTE, other)
 926
 927    def __gt__(self, other: t.Any) -> GT:
 928        return self._binop(GT, other)
 929
 930    def __ge__(self, other: t.Any) -> GTE:
 931        return self._binop(GTE, other)
 932
 933    def __add__(self, other: t.Any) -> Add:
 934        return self._binop(Add, other)
 935
 936    def __radd__(self, other: t.Any) -> Add:
 937        return self._binop(Add, other, reverse=True)
 938
 939    def __sub__(self, other: t.Any) -> Sub:
 940        return self._binop(Sub, other)
 941
 942    def __rsub__(self, other: t.Any) -> Sub:
 943        return self._binop(Sub, other, reverse=True)
 944
 945    def __mul__(self, other: t.Any) -> Mul:
 946        return self._binop(Mul, other)
 947
 948    def __rmul__(self, other: t.Any) -> Mul:
 949        return self._binop(Mul, other, reverse=True)
 950
 951    def __truediv__(self, other: t.Any) -> Div:
 952        return self._binop(Div, other)
 953
 954    def __rtruediv__(self, other: t.Any) -> Div:
 955        return self._binop(Div, other, reverse=True)
 956
 957    def __floordiv__(self, other: t.Any) -> IntDiv:
 958        return self._binop(IntDiv, other)
 959
 960    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 961        return self._binop(IntDiv, other, reverse=True)
 962
 963    def __mod__(self, other: t.Any) -> Mod:
 964        return self._binop(Mod, other)
 965
 966    def __rmod__(self, other: t.Any) -> Mod:
 967        return self._binop(Mod, other, reverse=True)
 968
 969    def __pow__(self, other: t.Any) -> Pow:
 970        return self._binop(Pow, other)
 971
 972    def __rpow__(self, other: t.Any) -> Pow:
 973        return self._binop(Pow, other, reverse=True)
 974
 975    def __and__(self, other: t.Any) -> And:
 976        return self._binop(And, other)
 977
 978    def __rand__(self, other: t.Any) -> And:
 979        return self._binop(And, other, reverse=True)
 980
 981    def __or__(self, other: t.Any) -> Or:
 982        return self._binop(Or, other)
 983
 984    def __ror__(self, other: t.Any) -> Or:
 985        return self._binop(Or, other, reverse=True)
 986
 987    def __neg__(self) -> Neg:
 988        return Neg(this=_wrap(self.copy(), Binary))
 989
 990    def __invert__(self) -> Not:
 991        return not_(self.copy())
 992
 993
 994IntoType = t.Union[
 995    str,
 996    t.Type[Expression],
 997    t.Collection[t.Union[str, t.Type[Expression]]],
 998]
 999ExpOrStr = t.Union[str, Expression]
1000
1001
1002class Condition(Expression):
1003    """Logical conditions like x AND y, or simply x"""
1004
1005
1006class Predicate(Condition):
1007    """Relationships like x = y, x > 1, x >= y."""
1008
1009
1010class DerivedTable(Expression):
1011    @property
1012    def selects(self) -> t.List[Expression]:
1013        return self.this.selects if isinstance(self.this, Query) else []
1014
1015    @property
1016    def named_selects(self) -> t.List[str]:
1017        return [select.output_name for select in self.selects]
1018
1019
1020class Query(Expression):
1021    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
1022        """
1023        Returns a `Subquery` that wraps around this query.
1024
1025        Example:
1026            >>> subquery = Select().select("x").from_("tbl").subquery()
1027            >>> Select().select("x").from_(subquery).sql()
1028            'SELECT x FROM (SELECT x FROM tbl)'
1029
1030        Args:
1031            alias: an optional alias for the subquery.
1032            copy: if `False`, modify this expression instance in-place.
1033        """
1034        instance = maybe_copy(self, copy)
1035        if not isinstance(alias, Expression):
1036            alias = TableAlias(this=to_identifier(alias)) if alias else None
1037
1038        return Subquery(this=instance, alias=alias)
1039
1040    def limit(
1041        self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1042    ) -> Q:
1043        """
1044        Adds a LIMIT clause to this query.
1045
1046        Example:
1047            >>> select("1").union(select("1")).limit(1).sql()
1048            'SELECT 1 UNION SELECT 1 LIMIT 1'
1049
1050        Args:
1051            expression: the SQL code string to parse.
1052                This can also be an integer.
1053                If a `Limit` instance is passed, it will be used as-is.
1054                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1055            dialect: the dialect used to parse the input expression.
1056            copy: if `False`, modify this expression instance in-place.
1057            opts: other options to use to parse the input expressions.
1058
1059        Returns:
1060            A limited Select expression.
1061        """
1062        return _apply_builder(
1063            expression=expression,
1064            instance=self,
1065            arg="limit",
1066            into=Limit,
1067            prefix="LIMIT",
1068            dialect=dialect,
1069            copy=copy,
1070            into_arg="expression",
1071            **opts,
1072        )
1073
1074    def offset(
1075        self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1076    ) -> Q:
1077        """
1078        Set the OFFSET expression.
1079
1080        Example:
1081            >>> Select().from_("tbl").select("x").offset(10).sql()
1082            'SELECT x FROM tbl OFFSET 10'
1083
1084        Args:
1085            expression: the SQL code string to parse.
1086                This can also be an integer.
1087                If a `Offset` instance is passed, this is used as-is.
1088                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
1089            dialect: the dialect used to parse the input expression.
1090            copy: if `False`, modify this expression instance in-place.
1091            opts: other options to use to parse the input expressions.
1092
1093        Returns:
1094            The modified Select expression.
1095        """
1096        return _apply_builder(
1097            expression=expression,
1098            instance=self,
1099            arg="offset",
1100            into=Offset,
1101            prefix="OFFSET",
1102            dialect=dialect,
1103            copy=copy,
1104            into_arg="expression",
1105            **opts,
1106        )
1107
1108    def order_by(
1109        self: Q,
1110        *expressions: t.Optional[ExpOrStr],
1111        append: bool = True,
1112        dialect: DialectType = None,
1113        copy: bool = True,
1114        **opts,
1115    ) -> Q:
1116        """
1117        Set the ORDER BY expression.
1118
1119        Example:
1120            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
1121            'SELECT x FROM tbl ORDER BY x DESC'
1122
1123        Args:
1124            *expressions: the SQL code strings to parse.
1125                If a `Group` instance is passed, this is used as-is.
1126                If another `Expression` instance is passed, it will be wrapped in a `Order`.
1127            append: if `True`, add to any existing expressions.
1128                Otherwise, this flattens all the `Order` expression into a single expression.
1129            dialect: the dialect used to parse the input expression.
1130            copy: if `False`, modify this expression instance in-place.
1131            opts: other options to use to parse the input expressions.
1132
1133        Returns:
1134            The modified Select expression.
1135        """
1136        return _apply_child_list_builder(
1137            *expressions,
1138            instance=self,
1139            arg="order",
1140            append=append,
1141            copy=copy,
1142            prefix="ORDER BY",
1143            into=Order,
1144            dialect=dialect,
1145            **opts,
1146        )
1147
1148    @property
1149    def ctes(self) -> t.List[CTE]:
1150        """Returns a list of all the CTEs attached to this query."""
1151        with_ = self.args.get("with")
1152        return with_.expressions if with_ else []
1153
1154    @property
1155    def selects(self) -> t.List[Expression]:
1156        """Returns the query's projections."""
1157        raise NotImplementedError("Query objects must implement `selects`")
1158
1159    @property
1160    def named_selects(self) -> t.List[str]:
1161        """Returns the output names of the query's projections."""
1162        raise NotImplementedError("Query objects must implement `named_selects`")
1163
1164    def select(
1165        self: Q,
1166        *expressions: t.Optional[ExpOrStr],
1167        append: bool = True,
1168        dialect: DialectType = None,
1169        copy: bool = True,
1170        **opts,
1171    ) -> Q:
1172        """
1173        Append to or set the SELECT expressions.
1174
1175        Example:
1176            >>> Select().select("x", "y").sql()
1177            'SELECT x, y'
1178
1179        Args:
1180            *expressions: the SQL code strings to parse.
1181                If an `Expression` instance is passed, it will be used as-is.
1182            append: if `True`, add to any existing expressions.
1183                Otherwise, this resets the expressions.
1184            dialect: the dialect used to parse the input expressions.
1185            copy: if `False`, modify this expression instance in-place.
1186            opts: other options to use to parse the input expressions.
1187
1188        Returns:
1189            The modified Query expression.
1190        """
1191        raise NotImplementedError("Query objects must implement `select`")
1192
1193    def with_(
1194        self: Q,
1195        alias: ExpOrStr,
1196        as_: ExpOrStr,
1197        recursive: t.Optional[bool] = None,
1198        append: bool = True,
1199        dialect: DialectType = None,
1200        copy: bool = True,
1201        **opts,
1202    ) -> Q:
1203        """
1204        Append to or set the common table expressions.
1205
1206        Example:
1207            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1208            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1209
1210        Args:
1211            alias: the SQL code string to parse as the table name.
1212                If an `Expression` instance is passed, this is used as-is.
1213            as_: the SQL code string to parse as the table expression.
1214                If an `Expression` instance is passed, it will be used as-is.
1215            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1216            append: if `True`, add to any existing expressions.
1217                Otherwise, this resets the expressions.
1218            dialect: the dialect used to parse the input expression.
1219            copy: if `False`, modify this expression instance in-place.
1220            opts: other options to use to parse the input expressions.
1221
1222        Returns:
1223            The modified expression.
1224        """
1225        return _apply_cte_builder(
1226            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1227        )
1228
1229    def union(
1230        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1231    ) -> Union:
1232        """
1233        Builds a UNION expression.
1234
1235        Example:
1236            >>> import sqlglot
1237            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
1238            'SELECT * FROM foo UNION SELECT * FROM bla'
1239
1240        Args:
1241            expression: the SQL code string.
1242                If an `Expression` instance is passed, it will be used as-is.
1243            distinct: set the DISTINCT flag if and only if this is true.
1244            dialect: the dialect used to parse the input expression.
1245            opts: other options to use to parse the input expressions.
1246
1247        Returns:
1248            The new Union expression.
1249        """
1250        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1251
1252    def intersect(
1253        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1254    ) -> Intersect:
1255        """
1256        Builds an INTERSECT expression.
1257
1258        Example:
1259            >>> import sqlglot
1260            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
1261            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
1262
1263        Args:
1264            expression: the SQL code string.
1265                If an `Expression` instance is passed, it will be used as-is.
1266            distinct: set the DISTINCT flag if and only if this is true.
1267            dialect: the dialect used to parse the input expression.
1268            opts: other options to use to parse the input expressions.
1269
1270        Returns:
1271            The new Intersect expression.
1272        """
1273        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1274
1275    def except_(
1276        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1277    ) -> Except:
1278        """
1279        Builds an EXCEPT expression.
1280
1281        Example:
1282            >>> import sqlglot
1283            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
1284            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
1285
1286        Args:
1287            expression: the SQL code string.
1288                If an `Expression` instance is passed, it will be used as-is.
1289            distinct: set the DISTINCT flag if and only if this is true.
1290            dialect: the dialect used to parse the input expression.
1291            opts: other options to use to parse the input expressions.
1292
1293        Returns:
1294            The new Except expression.
1295        """
1296        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1297
1298
1299class UDTF(DerivedTable):
1300    @property
1301    def selects(self) -> t.List[Expression]:
1302        alias = self.args.get("alias")
1303        return alias.columns if alias else []
1304
1305
1306class Cache(Expression):
1307    arg_types = {
1308        "this": True,
1309        "lazy": False,
1310        "options": False,
1311        "expression": False,
1312    }
1313
1314
1315class Uncache(Expression):
1316    arg_types = {"this": True, "exists": False}
1317
1318
1319class Refresh(Expression):
1320    pass
1321
1322
1323class DDL(Expression):
1324    @property
1325    def ctes(self) -> t.List[CTE]:
1326        """Returns a list of all the CTEs attached to this statement."""
1327        with_ = self.args.get("with")
1328        return with_.expressions if with_ else []
1329
1330    @property
1331    def selects(self) -> t.List[Expression]:
1332        """If this statement contains a query (e.g. a CTAS), this returns the query's projections."""
1333        return self.expression.selects if isinstance(self.expression, Query) else []
1334
1335    @property
1336    def named_selects(self) -> t.List[str]:
1337        """
1338        If this statement contains a query (e.g. a CTAS), this returns the output
1339        names of the query's projections.
1340        """
1341        return self.expression.named_selects if isinstance(self.expression, Query) else []
1342
1343
1344class DML(Expression):
1345    def returning(
1346        self,
1347        expression: ExpOrStr,
1348        dialect: DialectType = None,
1349        copy: bool = True,
1350        **opts,
1351    ) -> DML:
1352        """
1353        Set the RETURNING expression. Not supported by all dialects.
1354
1355        Example:
1356            >>> delete("tbl").returning("*", dialect="postgres").sql()
1357            'DELETE FROM tbl RETURNING *'
1358
1359        Args:
1360            expression: the SQL code strings to parse.
1361                If an `Expression` instance is passed, it will be used as-is.
1362            dialect: the dialect used to parse the input expressions.
1363            copy: if `False`, modify this expression instance in-place.
1364            opts: other options to use to parse the input expressions.
1365
1366        Returns:
1367            Delete: the modified expression.
1368        """
1369        return _apply_builder(
1370            expression=expression,
1371            instance=self,
1372            arg="returning",
1373            prefix="RETURNING",
1374            dialect=dialect,
1375            copy=copy,
1376            into=Returning,
1377            **opts,
1378        )
1379
1380
1381class Create(DDL):
1382    arg_types = {
1383        "with": False,
1384        "this": True,
1385        "kind": True,
1386        "expression": False,
1387        "exists": False,
1388        "properties": False,
1389        "replace": False,
1390        "refresh": False,
1391        "unique": False,
1392        "indexes": False,
1393        "no_schema_binding": False,
1394        "begin": False,
1395        "end": False,
1396        "clone": False,
1397        "concurrently": False,
1398        "clustered": False,
1399    }
1400
1401    @property
1402    def kind(self) -> t.Optional[str]:
1403        kind = self.args.get("kind")
1404        return kind and kind.upper()
1405
1406
1407class SequenceProperties(Expression):
1408    arg_types = {
1409        "increment": False,
1410        "minvalue": False,
1411        "maxvalue": False,
1412        "cache": False,
1413        "start": False,
1414        "owned": False,
1415        "options": False,
1416    }
1417
1418
1419class TruncateTable(Expression):
1420    arg_types = {
1421        "expressions": True,
1422        "is_database": False,
1423        "exists": False,
1424        "only": False,
1425        "cluster": False,
1426        "identity": False,
1427        "option": False,
1428        "partition": False,
1429    }
1430
1431
1432# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1433# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1434# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1435class Clone(Expression):
1436    arg_types = {"this": True, "shallow": False, "copy": False}
1437
1438
1439class Describe(Expression):
1440    arg_types = {"this": True, "style": False, "kind": False, "expressions": False}
1441
1442
1443# https://duckdb.org/docs/guides/meta/summarize.html
1444class Summarize(Expression):
1445    arg_types = {"this": True, "table": False}
1446
1447
1448class Kill(Expression):
1449    arg_types = {"this": True, "kind": False}
1450
1451
1452class Pragma(Expression):
1453    pass
1454
1455
1456class Declare(Expression):
1457    arg_types = {"expressions": True}
1458
1459
1460class DeclareItem(Expression):
1461    arg_types = {"this": True, "kind": True, "default": False}
1462
1463
1464class Set(Expression):
1465    arg_types = {"expressions": False, "unset": False, "tag": False}
1466
1467
1468class Heredoc(Expression):
1469    arg_types = {"this": True, "tag": False}
1470
1471
1472class SetItem(Expression):
1473    arg_types = {
1474        "this": False,
1475        "expressions": False,
1476        "kind": False,
1477        "collate": False,  # MySQL SET NAMES statement
1478        "global": False,
1479    }
1480
1481
1482class Show(Expression):
1483    arg_types = {
1484        "this": True,
1485        "history": False,
1486        "terse": False,
1487        "target": False,
1488        "offset": False,
1489        "starts_with": False,
1490        "limit": False,
1491        "from": False,
1492        "like": False,
1493        "where": False,
1494        "db": False,
1495        "scope": False,
1496        "scope_kind": False,
1497        "full": False,
1498        "mutex": False,
1499        "query": False,
1500        "channel": False,
1501        "global": False,
1502        "log": False,
1503        "position": False,
1504        "types": False,
1505    }
1506
1507
1508class UserDefinedFunction(Expression):
1509    arg_types = {"this": True, "expressions": False, "wrapped": False}
1510
1511
1512class CharacterSet(Expression):
1513    arg_types = {"this": True, "default": False}
1514
1515
1516class With(Expression):
1517    arg_types = {"expressions": True, "recursive": False}
1518
1519    @property
1520    def recursive(self) -> bool:
1521        return bool(self.args.get("recursive"))
1522
1523
1524class WithinGroup(Expression):
1525    arg_types = {"this": True, "expression": False}
1526
1527
1528# clickhouse supports scalar ctes
1529# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1530class CTE(DerivedTable):
1531    arg_types = {
1532        "this": True,
1533        "alias": True,
1534        "scalar": False,
1535        "materialized": False,
1536    }
1537
1538
1539class ProjectionDef(Expression):
1540    arg_types = {"this": True, "expression": True}
1541
1542
1543class TableAlias(Expression):
1544    arg_types = {"this": False, "columns": False}
1545
1546    @property
1547    def columns(self):
1548        return self.args.get("columns") or []
1549
1550
1551class BitString(Condition):
1552    pass
1553
1554
1555class HexString(Condition):
1556    pass
1557
1558
1559class ByteString(Condition):
1560    pass
1561
1562
1563class RawString(Condition):
1564    pass
1565
1566
1567class UnicodeString(Condition):
1568    arg_types = {"this": True, "escape": False}
1569
1570
1571class Column(Condition):
1572    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1573
1574    @property
1575    def table(self) -> str:
1576        return self.text("table")
1577
1578    @property
1579    def db(self) -> str:
1580        return self.text("db")
1581
1582    @property
1583    def catalog(self) -> str:
1584        return self.text("catalog")
1585
1586    @property
1587    def output_name(self) -> str:
1588        return self.name
1589
1590    @property
1591    def parts(self) -> t.List[Identifier]:
1592        """Return the parts of a column in order catalog, db, table, name."""
1593        return [
1594            t.cast(Identifier, self.args[part])
1595            for part in ("catalog", "db", "table", "this")
1596            if self.args.get(part)
1597        ]
1598
1599    def to_dot(self) -> Dot | Identifier:
1600        """Converts the column into a dot expression."""
1601        parts = self.parts
1602        parent = self.parent
1603
1604        while parent:
1605            if isinstance(parent, Dot):
1606                parts.append(parent.expression)
1607            parent = parent.parent
1608
1609        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1610
1611
1612class ColumnPosition(Expression):
1613    arg_types = {"this": False, "position": True}
1614
1615
1616class ColumnDef(Expression):
1617    arg_types = {
1618        "this": True,
1619        "kind": False,
1620        "constraints": False,
1621        "exists": False,
1622        "position": False,
1623    }
1624
1625    @property
1626    def constraints(self) -> t.List[ColumnConstraint]:
1627        return self.args.get("constraints") or []
1628
1629    @property
1630    def kind(self) -> t.Optional[DataType]:
1631        return self.args.get("kind")
1632
1633
1634class AlterColumn(Expression):
1635    arg_types = {
1636        "this": True,
1637        "dtype": False,
1638        "collate": False,
1639        "using": False,
1640        "default": False,
1641        "drop": False,
1642        "comment": False,
1643        "allow_null": False,
1644    }
1645
1646
1647# https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html
1648class AlterDistStyle(Expression):
1649    pass
1650
1651
1652class AlterSortKey(Expression):
1653    arg_types = {"this": False, "expressions": False, "compound": False}
1654
1655
1656class AlterSet(Expression):
1657    arg_types = {
1658        "expressions": False,
1659        "option": False,
1660        "tablespace": False,
1661        "access_method": False,
1662        "file_format": False,
1663        "copy_options": False,
1664        "tag": False,
1665        "location": False,
1666        "serde": False,
1667    }
1668
1669
1670class RenameColumn(Expression):
1671    arg_types = {"this": True, "to": True, "exists": False}
1672
1673
1674class RenameTable(Expression):
1675    pass
1676
1677
1678class SwapTable(Expression):
1679    pass
1680
1681
1682class Comment(Expression):
1683    arg_types = {
1684        "this": True,
1685        "kind": True,
1686        "expression": True,
1687        "exists": False,
1688        "materialized": False,
1689    }
1690
1691
1692class Comprehension(Expression):
1693    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1694
1695
1696# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1697class MergeTreeTTLAction(Expression):
1698    arg_types = {
1699        "this": True,
1700        "delete": False,
1701        "recompress": False,
1702        "to_disk": False,
1703        "to_volume": False,
1704    }
1705
1706
1707# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1708class MergeTreeTTL(Expression):
1709    arg_types = {
1710        "expressions": True,
1711        "where": False,
1712        "group": False,
1713        "aggregates": False,
1714    }
1715
1716
1717# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1718class IndexConstraintOption(Expression):
1719    arg_types = {
1720        "key_block_size": False,
1721        "using": False,
1722        "parser": False,
1723        "comment": False,
1724        "visible": False,
1725        "engine_attr": False,
1726        "secondary_engine_attr": False,
1727    }
1728
1729
1730class ColumnConstraint(Expression):
1731    arg_types = {"this": False, "kind": True}
1732
1733    @property
1734    def kind(self) -> ColumnConstraintKind:
1735        return self.args["kind"]
1736
1737
1738class ColumnConstraintKind(Expression):
1739    pass
1740
1741
1742class AutoIncrementColumnConstraint(ColumnConstraintKind):
1743    pass
1744
1745
1746class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1747    arg_types = {"this": True, "expression": True}
1748
1749
1750class CaseSpecificColumnConstraint(ColumnConstraintKind):
1751    arg_types = {"not_": True}
1752
1753
1754class CharacterSetColumnConstraint(ColumnConstraintKind):
1755    arg_types = {"this": True}
1756
1757
1758class CheckColumnConstraint(ColumnConstraintKind):
1759    arg_types = {"this": True, "enforced": False}
1760
1761
1762class ClusteredColumnConstraint(ColumnConstraintKind):
1763    pass
1764
1765
1766class CollateColumnConstraint(ColumnConstraintKind):
1767    pass
1768
1769
1770class CommentColumnConstraint(ColumnConstraintKind):
1771    pass
1772
1773
1774class CompressColumnConstraint(ColumnConstraintKind):
1775    pass
1776
1777
1778class DateFormatColumnConstraint(ColumnConstraintKind):
1779    arg_types = {"this": True}
1780
1781
1782class DefaultColumnConstraint(ColumnConstraintKind):
1783    pass
1784
1785
1786class EncodeColumnConstraint(ColumnConstraintKind):
1787    pass
1788
1789
1790# https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
1791class ExcludeColumnConstraint(ColumnConstraintKind):
1792    pass
1793
1794
1795class EphemeralColumnConstraint(ColumnConstraintKind):
1796    arg_types = {"this": False}
1797
1798
1799class WithOperator(Expression):
1800    arg_types = {"this": True, "op": True}
1801
1802
1803class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1804    # this: True -> ALWAYS, this: False -> BY DEFAULT
1805    arg_types = {
1806        "this": False,
1807        "expression": False,
1808        "on_null": False,
1809        "start": False,
1810        "increment": False,
1811        "minvalue": False,
1812        "maxvalue": False,
1813        "cycle": False,
1814    }
1815
1816
1817class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1818    arg_types = {"start": False, "hidden": False}
1819
1820
1821# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1822# https://github.com/ClickHouse/ClickHouse/blob/master/src/Parsers/ParserCreateQuery.h#L646
1823class IndexColumnConstraint(ColumnConstraintKind):
1824    arg_types = {
1825        "this": False,
1826        "expressions": False,
1827        "kind": False,
1828        "index_type": False,
1829        "options": False,
1830        "expression": False,  # Clickhouse
1831        "granularity": False,
1832    }
1833
1834
1835class InlineLengthColumnConstraint(ColumnConstraintKind):
1836    pass
1837
1838
1839class NonClusteredColumnConstraint(ColumnConstraintKind):
1840    pass
1841
1842
1843class NotForReplicationColumnConstraint(ColumnConstraintKind):
1844    arg_types = {}
1845
1846
1847# https://docs.snowflake.com/en/sql-reference/sql/create-table
1848class MaskingPolicyColumnConstraint(ColumnConstraintKind):
1849    arg_types = {"this": True, "expressions": False}
1850
1851
1852class NotNullColumnConstraint(ColumnConstraintKind):
1853    arg_types = {"allow_null": False}
1854
1855
1856# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1857class OnUpdateColumnConstraint(ColumnConstraintKind):
1858    pass
1859
1860
1861# https://docs.snowflake.com/en/sql-reference/sql/create-table
1862class TagColumnConstraint(ColumnConstraintKind):
1863    arg_types = {"expressions": True}
1864
1865
1866# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1867class TransformColumnConstraint(ColumnConstraintKind):
1868    pass
1869
1870
1871class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1872    arg_types = {"desc": False}
1873
1874
1875class TitleColumnConstraint(ColumnConstraintKind):
1876    pass
1877
1878
1879class UniqueColumnConstraint(ColumnConstraintKind):
1880    arg_types = {"this": False, "index_type": False, "on_conflict": False, "nulls": False}
1881
1882
1883class UppercaseColumnConstraint(ColumnConstraintKind):
1884    arg_types: t.Dict[str, t.Any] = {}
1885
1886
1887class PathColumnConstraint(ColumnConstraintKind):
1888    pass
1889
1890
1891# https://docs.snowflake.com/en/sql-reference/sql/create-table
1892class ProjectionPolicyColumnConstraint(ColumnConstraintKind):
1893    pass
1894
1895
1896# computed column expression
1897# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1898class ComputedColumnConstraint(ColumnConstraintKind):
1899    arg_types = {"this": True, "persisted": False, "not_null": False}
1900
1901
1902class Constraint(Expression):
1903    arg_types = {"this": True, "expressions": True}
1904
1905
1906class Delete(DML):
1907    arg_types = {
1908        "with": False,
1909        "this": False,
1910        "using": False,
1911        "where": False,
1912        "returning": False,
1913        "limit": False,
1914        "tables": False,  # Multiple-Table Syntax (MySQL)
1915    }
1916
1917    def delete(
1918        self,
1919        table: ExpOrStr,
1920        dialect: DialectType = None,
1921        copy: bool = True,
1922        **opts,
1923    ) -> Delete:
1924        """
1925        Create a DELETE expression or replace the table on an existing DELETE expression.
1926
1927        Example:
1928            >>> delete("tbl").sql()
1929            'DELETE FROM tbl'
1930
1931        Args:
1932            table: the table from which to delete.
1933            dialect: the dialect used to parse the input expression.
1934            copy: if `False`, modify this expression instance in-place.
1935            opts: other options to use to parse the input expressions.
1936
1937        Returns:
1938            Delete: the modified expression.
1939        """
1940        return _apply_builder(
1941            expression=table,
1942            instance=self,
1943            arg="this",
1944            dialect=dialect,
1945            into=Table,
1946            copy=copy,
1947            **opts,
1948        )
1949
1950    def where(
1951        self,
1952        *expressions: t.Optional[ExpOrStr],
1953        append: bool = True,
1954        dialect: DialectType = None,
1955        copy: bool = True,
1956        **opts,
1957    ) -> Delete:
1958        """
1959        Append to or set the WHERE expressions.
1960
1961        Example:
1962            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1963            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1964
1965        Args:
1966            *expressions: the SQL code strings to parse.
1967                If an `Expression` instance is passed, it will be used as-is.
1968                Multiple expressions are combined with an AND operator.
1969            append: if `True`, AND the new expressions to any existing expression.
1970                Otherwise, this resets the expression.
1971            dialect: the dialect used to parse the input expressions.
1972            copy: if `False`, modify this expression instance in-place.
1973            opts: other options to use to parse the input expressions.
1974
1975        Returns:
1976            Delete: the modified expression.
1977        """
1978        return _apply_conjunction_builder(
1979            *expressions,
1980            instance=self,
1981            arg="where",
1982            append=append,
1983            into=Where,
1984            dialect=dialect,
1985            copy=copy,
1986            **opts,
1987        )
1988
1989
1990class Drop(Expression):
1991    arg_types = {
1992        "this": False,
1993        "kind": False,
1994        "expressions": False,
1995        "exists": False,
1996        "temporary": False,
1997        "materialized": False,
1998        "cascade": False,
1999        "constraints": False,
2000        "purge": False,
2001        "cluster": False,
2002    }
2003
2004
2005class Filter(Expression):
2006    arg_types = {"this": True, "expression": True}
2007
2008
2009class Check(Expression):
2010    pass
2011
2012
2013class Changes(Expression):
2014    arg_types = {"information": True, "at_before": False, "end": False}
2015
2016
2017# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
2018class Connect(Expression):
2019    arg_types = {"start": False, "connect": True, "nocycle": False}
2020
2021
2022class CopyParameter(Expression):
2023    arg_types = {"this": True, "expression": False, "expressions": False}
2024
2025
2026class Copy(DML):
2027    arg_types = {
2028        "this": True,
2029        "kind": True,
2030        "files": True,
2031        "credentials": False,
2032        "format": False,
2033        "params": False,
2034    }
2035
2036
2037class Credentials(Expression):
2038    arg_types = {
2039        "credentials": False,
2040        "encryption": False,
2041        "storage": False,
2042        "iam_role": False,
2043        "region": False,
2044    }
2045
2046
2047class Prior(Expression):
2048    pass
2049
2050
2051class Directory(Expression):
2052    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
2053    arg_types = {"this": True, "local": False, "row_format": False}
2054
2055
2056class ForeignKey(Expression):
2057    arg_types = {
2058        "expressions": True,
2059        "reference": False,
2060        "delete": False,
2061        "update": False,
2062    }
2063
2064
2065class ColumnPrefix(Expression):
2066    arg_types = {"this": True, "expression": True}
2067
2068
2069class PrimaryKey(Expression):
2070    arg_types = {"expressions": True, "options": False}
2071
2072
2073# https://www.postgresql.org/docs/9.1/sql-selectinto.html
2074# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
2075class Into(Expression):
2076    arg_types = {"this": True, "temporary": False, "unlogged": False}
2077
2078
2079class From(Expression):
2080    @property
2081    def name(self) -> str:
2082        return self.this.name
2083
2084    @property
2085    def alias_or_name(self) -> str:
2086        return self.this.alias_or_name
2087
2088
2089class Having(Expression):
2090    pass
2091
2092
2093class Hint(Expression):
2094    arg_types = {"expressions": True}
2095
2096
2097class JoinHint(Expression):
2098    arg_types = {"this": True, "expressions": True}
2099
2100
2101class Identifier(Expression):
2102    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
2103
2104    @property
2105    def quoted(self) -> bool:
2106        return bool(self.args.get("quoted"))
2107
2108    @property
2109    def hashable_args(self) -> t.Any:
2110        return (self.this, self.quoted)
2111
2112    @property
2113    def output_name(self) -> str:
2114        return self.name
2115
2116
2117# https://www.postgresql.org/docs/current/indexes-opclass.html
2118class Opclass(Expression):
2119    arg_types = {"this": True, "expression": True}
2120
2121
2122class Index(Expression):
2123    arg_types = {
2124        "this": False,
2125        "table": False,
2126        "unique": False,
2127        "primary": False,
2128        "amp": False,  # teradata
2129        "params": False,
2130    }
2131
2132
2133class IndexParameters(Expression):
2134    arg_types = {
2135        "using": False,
2136        "include": False,
2137        "columns": False,
2138        "with_storage": False,
2139        "partition_by": False,
2140        "tablespace": False,
2141        "where": False,
2142        "on": False,
2143    }
2144
2145
2146class Insert(DDL, DML):
2147    arg_types = {
2148        "hint": False,
2149        "with": False,
2150        "is_function": False,
2151        "this": False,
2152        "expression": False,
2153        "conflict": False,
2154        "returning": False,
2155        "overwrite": False,
2156        "exists": False,
2157        "alternative": False,
2158        "where": False,
2159        "ignore": False,
2160        "by_name": False,
2161        "stored": False,
2162    }
2163
2164    def with_(
2165        self,
2166        alias: ExpOrStr,
2167        as_: ExpOrStr,
2168        recursive: t.Optional[bool] = None,
2169        append: bool = True,
2170        dialect: DialectType = None,
2171        copy: bool = True,
2172        **opts,
2173    ) -> Insert:
2174        """
2175        Append to or set the common table expressions.
2176
2177        Example:
2178            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
2179            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
2180
2181        Args:
2182            alias: the SQL code string to parse as the table name.
2183                If an `Expression` instance is passed, this is used as-is.
2184            as_: the SQL code string to parse as the table expression.
2185                If an `Expression` instance is passed, it will be used as-is.
2186            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2187            append: if `True`, add to any existing expressions.
2188                Otherwise, this resets the expressions.
2189            dialect: the dialect used to parse the input expression.
2190            copy: if `False`, modify this expression instance in-place.
2191            opts: other options to use to parse the input expressions.
2192
2193        Returns:
2194            The modified expression.
2195        """
2196        return _apply_cte_builder(
2197            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2198        )
2199
2200
2201class OnConflict(Expression):
2202    arg_types = {
2203        "duplicate": False,
2204        "expressions": False,
2205        "action": False,
2206        "conflict_keys": False,
2207        "constraint": False,
2208    }
2209
2210
2211class Returning(Expression):
2212    arg_types = {"expressions": True, "into": False}
2213
2214
2215# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
2216class Introducer(Expression):
2217    arg_types = {"this": True, "expression": True}
2218
2219
2220# national char, like n'utf8'
2221class National(Expression):
2222    pass
2223
2224
2225class LoadData(Expression):
2226    arg_types = {
2227        "this": True,
2228        "local": False,
2229        "overwrite": False,
2230        "inpath": True,
2231        "partition": False,
2232        "input_format": False,
2233        "serde": False,
2234    }
2235
2236
2237class Partition(Expression):
2238    arg_types = {"expressions": True}
2239
2240
2241class PartitionRange(Expression):
2242    arg_types = {"this": True, "expression": True}
2243
2244
2245# https://clickhouse.com/docs/en/sql-reference/statements/alter/partition#how-to-set-partition-expression
2246class PartitionId(Expression):
2247    pass
2248
2249
2250class Fetch(Expression):
2251    arg_types = {
2252        "direction": False,
2253        "count": False,
2254        "percent": False,
2255        "with_ties": False,
2256    }
2257
2258
2259class Group(Expression):
2260    arg_types = {
2261        "expressions": False,
2262        "grouping_sets": False,
2263        "cube": False,
2264        "rollup": False,
2265        "totals": False,
2266        "all": False,
2267    }
2268
2269
2270class Lambda(Expression):
2271    arg_types = {"this": True, "expressions": True}
2272
2273
2274class Limit(Expression):
2275    arg_types = {"this": False, "expression": True, "offset": False, "expressions": False}
2276
2277
2278class Literal(Condition):
2279    arg_types = {"this": True, "is_string": True}
2280
2281    @property
2282    def hashable_args(self) -> t.Any:
2283        return (self.this, self.args.get("is_string"))
2284
2285    @classmethod
2286    def number(cls, number) -> Literal:
2287        return cls(this=str(number), is_string=False)
2288
2289    @classmethod
2290    def string(cls, string) -> Literal:
2291        return cls(this=str(string), is_string=True)
2292
2293    @property
2294    def output_name(self) -> str:
2295        return self.name
2296
2297    def to_py(self) -> int | str | Decimal:
2298        if self.is_number:
2299            try:
2300                return int(self.this)
2301            except ValueError:
2302                return Decimal(self.this)
2303        return self.this
2304
2305
2306class Join(Expression):
2307    arg_types = {
2308        "this": True,
2309        "on": False,
2310        "side": False,
2311        "kind": False,
2312        "using": False,
2313        "method": False,
2314        "global": False,
2315        "hint": False,
2316        "match_condition": False,  # Snowflake
2317    }
2318
2319    @property
2320    def method(self) -> str:
2321        return self.text("method").upper()
2322
2323    @property
2324    def kind(self) -> str:
2325        return self.text("kind").upper()
2326
2327    @property
2328    def side(self) -> str:
2329        return self.text("side").upper()
2330
2331    @property
2332    def hint(self) -> str:
2333        return self.text("hint").upper()
2334
2335    @property
2336    def alias_or_name(self) -> str:
2337        return self.this.alias_or_name
2338
2339    def on(
2340        self,
2341        *expressions: t.Optional[ExpOrStr],
2342        append: bool = True,
2343        dialect: DialectType = None,
2344        copy: bool = True,
2345        **opts,
2346    ) -> Join:
2347        """
2348        Append to or set the ON expressions.
2349
2350        Example:
2351            >>> import sqlglot
2352            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
2353            'JOIN x ON y = 1'
2354
2355        Args:
2356            *expressions: the SQL code strings to parse.
2357                If an `Expression` instance is passed, it will be used as-is.
2358                Multiple expressions are combined with an AND operator.
2359            append: if `True`, AND the new expressions to any existing expression.
2360                Otherwise, this resets the expression.
2361            dialect: the dialect used to parse the input expressions.
2362            copy: if `False`, modify this expression instance in-place.
2363            opts: other options to use to parse the input expressions.
2364
2365        Returns:
2366            The modified Join expression.
2367        """
2368        join = _apply_conjunction_builder(
2369            *expressions,
2370            instance=self,
2371            arg="on",
2372            append=append,
2373            dialect=dialect,
2374            copy=copy,
2375            **opts,
2376        )
2377
2378        if join.kind == "CROSS":
2379            join.set("kind", None)
2380
2381        return join
2382
2383    def using(
2384        self,
2385        *expressions: t.Optional[ExpOrStr],
2386        append: bool = True,
2387        dialect: DialectType = None,
2388        copy: bool = True,
2389        **opts,
2390    ) -> Join:
2391        """
2392        Append to or set the USING expressions.
2393
2394        Example:
2395            >>> import sqlglot
2396            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
2397            'JOIN x USING (foo, bla)'
2398
2399        Args:
2400            *expressions: the SQL code strings to parse.
2401                If an `Expression` instance is passed, it will be used as-is.
2402            append: if `True`, concatenate the new expressions to the existing "using" list.
2403                Otherwise, this resets the expression.
2404            dialect: the dialect used to parse the input expressions.
2405            copy: if `False`, modify this expression instance in-place.
2406            opts: other options to use to parse the input expressions.
2407
2408        Returns:
2409            The modified Join expression.
2410        """
2411        join = _apply_list_builder(
2412            *expressions,
2413            instance=self,
2414            arg="using",
2415            append=append,
2416            dialect=dialect,
2417            copy=copy,
2418            **opts,
2419        )
2420
2421        if join.kind == "CROSS":
2422            join.set("kind", None)
2423
2424        return join
2425
2426
2427class Lateral(UDTF):
2428    arg_types = {
2429        "this": True,
2430        "view": False,
2431        "outer": False,
2432        "alias": False,
2433        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
2434    }
2435
2436
2437class MatchRecognizeMeasure(Expression):
2438    arg_types = {
2439        "this": True,
2440        "window_frame": False,
2441    }
2442
2443
2444class MatchRecognize(Expression):
2445    arg_types = {
2446        "partition_by": False,
2447        "order": False,
2448        "measures": False,
2449        "rows": False,
2450        "after": False,
2451        "pattern": False,
2452        "define": False,
2453        "alias": False,
2454    }
2455
2456
2457# Clickhouse FROM FINAL modifier
2458# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
2459class Final(Expression):
2460    pass
2461
2462
2463class Offset(Expression):
2464    arg_types = {"this": False, "expression": True, "expressions": False}
2465
2466
2467class Order(Expression):
2468    arg_types = {
2469        "this": False,
2470        "expressions": True,
2471        "interpolate": False,
2472        "siblings": False,
2473    }
2474
2475
2476# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
2477class WithFill(Expression):
2478    arg_types = {"from": False, "to": False, "step": False}
2479
2480
2481# hive specific sorts
2482# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
2483class Cluster(Order):
2484    pass
2485
2486
2487class Distribute(Order):
2488    pass
2489
2490
2491class Sort(Order):
2492    pass
2493
2494
2495class Ordered(Expression):
2496    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
2497
2498
2499class Property(Expression):
2500    arg_types = {"this": True, "value": True}
2501
2502
2503class AllowedValuesProperty(Expression):
2504    arg_types = {"expressions": True}
2505
2506
2507class AlgorithmProperty(Property):
2508    arg_types = {"this": True}
2509
2510
2511class AutoIncrementProperty(Property):
2512    arg_types = {"this": True}
2513
2514
2515# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2516class AutoRefreshProperty(Property):
2517    arg_types = {"this": True}
2518
2519
2520class BackupProperty(Property):
2521    arg_types = {"this": True}
2522
2523
2524class BlockCompressionProperty(Property):
2525    arg_types = {
2526        "autotemp": False,
2527        "always": False,
2528        "default": False,
2529        "manual": False,
2530        "never": False,
2531    }
2532
2533
2534class CharacterSetProperty(Property):
2535    arg_types = {"this": True, "default": True}
2536
2537
2538class ChecksumProperty(Property):
2539    arg_types = {"on": False, "default": False}
2540
2541
2542class CollateProperty(Property):
2543    arg_types = {"this": True, "default": False}
2544
2545
2546class CopyGrantsProperty(Property):
2547    arg_types = {}
2548
2549
2550class DataBlocksizeProperty(Property):
2551    arg_types = {
2552        "size": False,
2553        "units": False,
2554        "minimum": False,
2555        "maximum": False,
2556        "default": False,
2557    }
2558
2559
2560class DataDeletionProperty(Property):
2561    arg_types = {"on": True, "filter_col": False, "retention_period": False}
2562
2563
2564class DefinerProperty(Property):
2565    arg_types = {"this": True}
2566
2567
2568class DistKeyProperty(Property):
2569    arg_types = {"this": True}
2570
2571
2572class DistStyleProperty(Property):
2573    arg_types = {"this": True}
2574
2575
2576class EngineProperty(Property):
2577    arg_types = {"this": True}
2578
2579
2580class HeapProperty(Property):
2581    arg_types = {}
2582
2583
2584class ToTableProperty(Property):
2585    arg_types = {"this": True}
2586
2587
2588class ExecuteAsProperty(Property):
2589    arg_types = {"this": True}
2590
2591
2592class ExternalProperty(Property):
2593    arg_types = {"this": False}
2594
2595
2596class FallbackProperty(Property):
2597    arg_types = {"no": True, "protection": False}
2598
2599
2600class FileFormatProperty(Property):
2601    arg_types = {"this": True}
2602
2603
2604class FreespaceProperty(Property):
2605    arg_types = {"this": True, "percent": False}
2606
2607
2608class GlobalProperty(Property):
2609    arg_types = {}
2610
2611
2612class IcebergProperty(Property):
2613    arg_types = {}
2614
2615
2616class InheritsProperty(Property):
2617    arg_types = {"expressions": True}
2618
2619
2620class InputModelProperty(Property):
2621    arg_types = {"this": True}
2622
2623
2624class OutputModelProperty(Property):
2625    arg_types = {"this": True}
2626
2627
2628class IsolatedLoadingProperty(Property):
2629    arg_types = {"no": False, "concurrent": False, "target": False}
2630
2631
2632class JournalProperty(Property):
2633    arg_types = {
2634        "no": False,
2635        "dual": False,
2636        "before": False,
2637        "local": False,
2638        "after": False,
2639    }
2640
2641
2642class LanguageProperty(Property):
2643    arg_types = {"this": True}
2644
2645
2646# spark ddl
2647class ClusteredByProperty(Property):
2648    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2649
2650
2651class DictProperty(Property):
2652    arg_types = {"this": True, "kind": True, "settings": False}
2653
2654
2655class DictSubProperty(Property):
2656    pass
2657
2658
2659class DictRange(Property):
2660    arg_types = {"this": True, "min": True, "max": True}
2661
2662
2663class DynamicProperty(Property):
2664    arg_types = {}
2665
2666
2667# Clickhouse CREATE ... ON CLUSTER modifier
2668# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2669class OnCluster(Property):
2670    arg_types = {"this": True}
2671
2672
2673# Clickhouse EMPTY table "property"
2674class EmptyProperty(Property):
2675    arg_types = {}
2676
2677
2678class LikeProperty(Property):
2679    arg_types = {"this": True, "expressions": False}
2680
2681
2682class LocationProperty(Property):
2683    arg_types = {"this": True}
2684
2685
2686class LockProperty(Property):
2687    arg_types = {"this": True}
2688
2689
2690class LockingProperty(Property):
2691    arg_types = {
2692        "this": False,
2693        "kind": True,
2694        "for_or_in": False,
2695        "lock_type": True,
2696        "override": False,
2697    }
2698
2699
2700class LogProperty(Property):
2701    arg_types = {"no": True}
2702
2703
2704class MaterializedProperty(Property):
2705    arg_types = {"this": False}
2706
2707
2708class MergeBlockRatioProperty(Property):
2709    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2710
2711
2712class NoPrimaryIndexProperty(Property):
2713    arg_types = {}
2714
2715
2716class OnProperty(Property):
2717    arg_types = {"this": True}
2718
2719
2720class OnCommitProperty(Property):
2721    arg_types = {"delete": False}
2722
2723
2724class PartitionedByProperty(Property):
2725    arg_types = {"this": True}
2726
2727
2728# https://www.postgresql.org/docs/current/sql-createtable.html
2729class PartitionBoundSpec(Expression):
2730    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2731    arg_types = {
2732        "this": False,
2733        "expression": False,
2734        "from_expressions": False,
2735        "to_expressions": False,
2736    }
2737
2738
2739class PartitionedOfProperty(Property):
2740    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2741    arg_types = {"this": True, "expression": True}
2742
2743
2744class StreamingTableProperty(Property):
2745    arg_types = {}
2746
2747
2748class RemoteWithConnectionModelProperty(Property):
2749    arg_types = {"this": True}
2750
2751
2752class ReturnsProperty(Property):
2753    arg_types = {"this": False, "is_table": False, "table": False, "null": False}
2754
2755
2756class StrictProperty(Property):
2757    arg_types = {}
2758
2759
2760class RowFormatProperty(Property):
2761    arg_types = {"this": True}
2762
2763
2764class RowFormatDelimitedProperty(Property):
2765    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2766    arg_types = {
2767        "fields": False,
2768        "escaped": False,
2769        "collection_items": False,
2770        "map_keys": False,
2771        "lines": False,
2772        "null": False,
2773        "serde": False,
2774    }
2775
2776
2777class RowFormatSerdeProperty(Property):
2778    arg_types = {"this": True, "serde_properties": False}
2779
2780
2781# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2782class QueryTransform(Expression):
2783    arg_types = {
2784        "expressions": True,
2785        "command_script": True,
2786        "schema": False,
2787        "row_format_before": False,
2788        "record_writer": False,
2789        "row_format_after": False,
2790        "record_reader": False,
2791    }
2792
2793
2794class SampleProperty(Property):
2795    arg_types = {"this": True}
2796
2797
2798class SchemaCommentProperty(Property):
2799    arg_types = {"this": True}
2800
2801
2802class SerdeProperties(Property):
2803    arg_types = {"expressions": True, "with": False}
2804
2805
2806class SetProperty(Property):
2807    arg_types = {"multi": True}
2808
2809
2810class SharingProperty(Property):
2811    arg_types = {"this": False}
2812
2813
2814class SetConfigProperty(Property):
2815    arg_types = {"this": True}
2816
2817
2818class SettingsProperty(Property):
2819    arg_types = {"expressions": True}
2820
2821
2822class SortKeyProperty(Property):
2823    arg_types = {"this": True, "compound": False}
2824
2825
2826class SqlReadWriteProperty(Property):
2827    arg_types = {"this": True}
2828
2829
2830class SqlSecurityProperty(Property):
2831    arg_types = {"definer": True}
2832
2833
2834class StabilityProperty(Property):
2835    arg_types = {"this": True}
2836
2837
2838class TemporaryProperty(Property):
2839    arg_types = {"this": False}
2840
2841
2842class SecureProperty(Property):
2843    arg_types = {}
2844
2845
2846class TransformModelProperty(Property):
2847    arg_types = {"expressions": True}
2848
2849
2850class TransientProperty(Property):
2851    arg_types = {"this": False}
2852
2853
2854class UnloggedProperty(Property):
2855    arg_types = {}
2856
2857
2858# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver16
2859class ViewAttributeProperty(Property):
2860    arg_types = {"this": True}
2861
2862
2863class VolatileProperty(Property):
2864    arg_types = {"this": False}
2865
2866
2867class WithDataProperty(Property):
2868    arg_types = {"no": True, "statistics": False}
2869
2870
2871class WithJournalTableProperty(Property):
2872    arg_types = {"this": True}
2873
2874
2875class WithSchemaBindingProperty(Property):
2876    arg_types = {"this": True}
2877
2878
2879class WithSystemVersioningProperty(Property):
2880    arg_types = {
2881        "on": False,
2882        "this": False,
2883        "data_consistency": False,
2884        "retention_period": False,
2885        "with": True,
2886    }
2887
2888
2889class Properties(Expression):
2890    arg_types = {"expressions": True}
2891
2892    NAME_TO_PROPERTY = {
2893        "ALGORITHM": AlgorithmProperty,
2894        "AUTO_INCREMENT": AutoIncrementProperty,
2895        "CHARACTER SET": CharacterSetProperty,
2896        "CLUSTERED_BY": ClusteredByProperty,
2897        "COLLATE": CollateProperty,
2898        "COMMENT": SchemaCommentProperty,
2899        "DEFINER": DefinerProperty,
2900        "DISTKEY": DistKeyProperty,
2901        "DISTSTYLE": DistStyleProperty,
2902        "ENGINE": EngineProperty,
2903        "EXECUTE AS": ExecuteAsProperty,
2904        "FORMAT": FileFormatProperty,
2905        "LANGUAGE": LanguageProperty,
2906        "LOCATION": LocationProperty,
2907        "LOCK": LockProperty,
2908        "PARTITIONED_BY": PartitionedByProperty,
2909        "RETURNS": ReturnsProperty,
2910        "ROW_FORMAT": RowFormatProperty,
2911        "SORTKEY": SortKeyProperty,
2912    }
2913
2914    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2915
2916    # CREATE property locations
2917    # Form: schema specified
2918    #   create [POST_CREATE]
2919    #     table a [POST_NAME]
2920    #     (b int) [POST_SCHEMA]
2921    #     with ([POST_WITH])
2922    #     index (b) [POST_INDEX]
2923    #
2924    # Form: alias selection
2925    #   create [POST_CREATE]
2926    #     table a [POST_NAME]
2927    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2928    #     index (c) [POST_INDEX]
2929    class Location(AutoName):
2930        POST_CREATE = auto()
2931        POST_NAME = auto()
2932        POST_SCHEMA = auto()
2933        POST_WITH = auto()
2934        POST_ALIAS = auto()
2935        POST_EXPRESSION = auto()
2936        POST_INDEX = auto()
2937        UNSUPPORTED = auto()
2938
2939    @classmethod
2940    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2941        expressions = []
2942        for key, value in properties_dict.items():
2943            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2944            if property_cls:
2945                expressions.append(property_cls(this=convert(value)))
2946            else:
2947                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2948
2949        return cls(expressions=expressions)
2950
2951
2952class Qualify(Expression):
2953    pass
2954
2955
2956class InputOutputFormat(Expression):
2957    arg_types = {"input_format": False, "output_format": False}
2958
2959
2960# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2961class Return(Expression):
2962    pass
2963
2964
2965class Reference(Expression):
2966    arg_types = {"this": True, "expressions": False, "options": False}
2967
2968
2969class Tuple(Expression):
2970    arg_types = {"expressions": False}
2971
2972    def isin(
2973        self,
2974        *expressions: t.Any,
2975        query: t.Optional[ExpOrStr] = None,
2976        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2977        copy: bool = True,
2978        **opts,
2979    ) -> In:
2980        return In(
2981            this=maybe_copy(self, copy),
2982            expressions=[convert(e, copy=copy) for e in expressions],
2983            query=maybe_parse(query, copy=copy, **opts) if query else None,
2984            unnest=(
2985                Unnest(
2986                    expressions=[
2987                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
2988                        for e in ensure_list(unnest)
2989                    ]
2990                )
2991                if unnest
2992                else None
2993            ),
2994        )
2995
2996
2997QUERY_MODIFIERS = {
2998    "match": False,
2999    "laterals": False,
3000    "joins": False,
3001    "connect": False,
3002    "pivots": False,
3003    "prewhere": False,
3004    "where": False,
3005    "group": False,
3006    "having": False,
3007    "qualify": False,
3008    "windows": False,
3009    "distribute": False,
3010    "sort": False,
3011    "cluster": False,
3012    "order": False,
3013    "limit": False,
3014    "offset": False,
3015    "locks": False,
3016    "sample": False,
3017    "settings": False,
3018    "format": False,
3019    "options": False,
3020}
3021
3022
3023# https://learn.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver16
3024# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16
3025class QueryOption(Expression):
3026    arg_types = {"this": True, "expression": False}
3027
3028
3029# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
3030class WithTableHint(Expression):
3031    arg_types = {"expressions": True}
3032
3033
3034# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
3035class IndexTableHint(Expression):
3036    arg_types = {"this": True, "expressions": False, "target": False}
3037
3038
3039# https://docs.snowflake.com/en/sql-reference/constructs/at-before
3040class HistoricalData(Expression):
3041    arg_types = {"this": True, "kind": True, "expression": True}
3042
3043
3044class Table(Expression):
3045    arg_types = {
3046        "this": False,
3047        "alias": False,
3048        "db": False,
3049        "catalog": False,
3050        "laterals": False,
3051        "joins": False,
3052        "pivots": False,
3053        "hints": False,
3054        "system_time": False,
3055        "version": False,
3056        "format": False,
3057        "pattern": False,
3058        "ordinality": False,
3059        "when": False,
3060        "only": False,
3061        "partition": False,
3062        "changes": False,
3063        "rows_from": False,
3064    }
3065
3066    @property
3067    def name(self) -> str:
3068        if isinstance(self.this, Func):
3069            return ""
3070        return self.this.name
3071
3072    @property
3073    def db(self) -> str:
3074        return self.text("db")
3075
3076    @property
3077    def catalog(self) -> str:
3078        return self.text("catalog")
3079
3080    @property
3081    def selects(self) -> t.List[Expression]:
3082        return []
3083
3084    @property
3085    def named_selects(self) -> t.List[str]:
3086        return []
3087
3088    @property
3089    def parts(self) -> t.List[Expression]:
3090        """Return the parts of a table in order catalog, db, table."""
3091        parts: t.List[Expression] = []
3092
3093        for arg in ("catalog", "db", "this"):
3094            part = self.args.get(arg)
3095
3096            if isinstance(part, Dot):
3097                parts.extend(part.flatten())
3098            elif isinstance(part, Expression):
3099                parts.append(part)
3100
3101        return parts
3102
3103    def to_column(self, copy: bool = True) -> Alias | Column | Dot:
3104        parts = self.parts
3105        col = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
3106        alias = self.args.get("alias")
3107        if alias:
3108            col = alias_(col, alias.this, copy=copy)
3109        return col
3110
3111
3112class SetOperation(Query):
3113    arg_types = {
3114        "with": False,
3115        "this": True,
3116        "expression": True,
3117        "distinct": False,
3118        "by_name": False,
3119        **QUERY_MODIFIERS,
3120    }
3121
3122    def select(
3123        self: S,
3124        *expressions: t.Optional[ExpOrStr],
3125        append: bool = True,
3126        dialect: DialectType = None,
3127        copy: bool = True,
3128        **opts,
3129    ) -> S:
3130        this = maybe_copy(self, copy)
3131        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3132        this.expression.unnest().select(
3133            *expressions, append=append, dialect=dialect, copy=False, **opts
3134        )
3135        return this
3136
3137    @property
3138    def named_selects(self) -> t.List[str]:
3139        return self.this.unnest().named_selects
3140
3141    @property
3142    def is_star(self) -> bool:
3143        return self.this.is_star or self.expression.is_star
3144
3145    @property
3146    def selects(self) -> t.List[Expression]:
3147        return self.this.unnest().selects
3148
3149    @property
3150    def left(self) -> Expression:
3151        return self.this
3152
3153    @property
3154    def right(self) -> Expression:
3155        return self.expression
3156
3157
3158class Union(SetOperation):
3159    pass
3160
3161
3162class Except(SetOperation):
3163    pass
3164
3165
3166class Intersect(SetOperation):
3167    pass
3168
3169
3170class Update(Expression):
3171    arg_types = {
3172        "with": False,
3173        "this": False,
3174        "expressions": True,
3175        "from": False,
3176        "where": False,
3177        "returning": False,
3178        "order": False,
3179        "limit": False,
3180    }
3181
3182
3183class Values(UDTF):
3184    arg_types = {"expressions": True, "alias": False}
3185
3186
3187class Var(Expression):
3188    pass
3189
3190
3191class Version(Expression):
3192    """
3193    Time travel, iceberg, bigquery etc
3194    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
3195    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
3196    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
3197    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
3198    this is either TIMESTAMP or VERSION
3199    kind is ("AS OF", "BETWEEN")
3200    """
3201
3202    arg_types = {"this": True, "kind": True, "expression": False}
3203
3204
3205class Schema(Expression):
3206    arg_types = {"this": False, "expressions": False}
3207
3208
3209# https://dev.mysql.com/doc/refman/8.0/en/select.html
3210# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
3211class Lock(Expression):
3212    arg_types = {"update": True, "expressions": False, "wait": False}
3213
3214
3215class Select(Query):
3216    arg_types = {
3217        "with": False,
3218        "kind": False,
3219        "expressions": False,
3220        "hint": False,
3221        "distinct": False,
3222        "into": False,
3223        "from": False,
3224        **QUERY_MODIFIERS,
3225    }
3226
3227    def from_(
3228        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
3229    ) -> Select:
3230        """
3231        Set the FROM expression.
3232
3233        Example:
3234            >>> Select().from_("tbl").select("x").sql()
3235            'SELECT x FROM tbl'
3236
3237        Args:
3238            expression : the SQL code strings to parse.
3239                If a `From` instance is passed, this is used as-is.
3240                If another `Expression` instance is passed, it will be wrapped in a `From`.
3241            dialect: the dialect used to parse the input expression.
3242            copy: if `False`, modify this expression instance in-place.
3243            opts: other options to use to parse the input expressions.
3244
3245        Returns:
3246            The modified Select expression.
3247        """
3248        return _apply_builder(
3249            expression=expression,
3250            instance=self,
3251            arg="from",
3252            into=From,
3253            prefix="FROM",
3254            dialect=dialect,
3255            copy=copy,
3256            **opts,
3257        )
3258
3259    def group_by(
3260        self,
3261        *expressions: t.Optional[ExpOrStr],
3262        append: bool = True,
3263        dialect: DialectType = None,
3264        copy: bool = True,
3265        **opts,
3266    ) -> Select:
3267        """
3268        Set the GROUP BY expression.
3269
3270        Example:
3271            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
3272            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
3273
3274        Args:
3275            *expressions: the SQL code strings to parse.
3276                If a `Group` instance is passed, this is used as-is.
3277                If another `Expression` instance is passed, it will be wrapped in a `Group`.
3278                If nothing is passed in then a group by is not applied to the expression
3279            append: if `True`, add to any existing expressions.
3280                Otherwise, this flattens all the `Group` expression into a single expression.
3281            dialect: the dialect used to parse the input expression.
3282            copy: if `False`, modify this expression instance in-place.
3283            opts: other options to use to parse the input expressions.
3284
3285        Returns:
3286            The modified Select expression.
3287        """
3288        if not expressions:
3289            return self if not copy else self.copy()
3290
3291        return _apply_child_list_builder(
3292            *expressions,
3293            instance=self,
3294            arg="group",
3295            append=append,
3296            copy=copy,
3297            prefix="GROUP BY",
3298            into=Group,
3299            dialect=dialect,
3300            **opts,
3301        )
3302
3303    def sort_by(
3304        self,
3305        *expressions: t.Optional[ExpOrStr],
3306        append: bool = True,
3307        dialect: DialectType = None,
3308        copy: bool = True,
3309        **opts,
3310    ) -> Select:
3311        """
3312        Set the SORT BY expression.
3313
3314        Example:
3315            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
3316            'SELECT x FROM tbl SORT BY x DESC'
3317
3318        Args:
3319            *expressions: the SQL code strings to parse.
3320                If a `Group` instance is passed, this is used as-is.
3321                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
3322            append: if `True`, add to any existing expressions.
3323                Otherwise, this flattens all the `Order` expression into a single expression.
3324            dialect: the dialect used to parse the input expression.
3325            copy: if `False`, modify this expression instance in-place.
3326            opts: other options to use to parse the input expressions.
3327
3328        Returns:
3329            The modified Select expression.
3330        """
3331        return _apply_child_list_builder(
3332            *expressions,
3333            instance=self,
3334            arg="sort",
3335            append=append,
3336            copy=copy,
3337            prefix="SORT BY",
3338            into=Sort,
3339            dialect=dialect,
3340            **opts,
3341        )
3342
3343    def cluster_by(
3344        self,
3345        *expressions: t.Optional[ExpOrStr],
3346        append: bool = True,
3347        dialect: DialectType = None,
3348        copy: bool = True,
3349        **opts,
3350    ) -> Select:
3351        """
3352        Set the CLUSTER BY expression.
3353
3354        Example:
3355            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
3356            'SELECT x FROM tbl CLUSTER BY x DESC'
3357
3358        Args:
3359            *expressions: the SQL code strings to parse.
3360                If a `Group` instance is passed, this is used as-is.
3361                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
3362            append: if `True`, add to any existing expressions.
3363                Otherwise, this flattens all the `Order` expression into a single expression.
3364            dialect: the dialect used to parse the input expression.
3365            copy: if `False`, modify this expression instance in-place.
3366            opts: other options to use to parse the input expressions.
3367
3368        Returns:
3369            The modified Select expression.
3370        """
3371        return _apply_child_list_builder(
3372            *expressions,
3373            instance=self,
3374            arg="cluster",
3375            append=append,
3376            copy=copy,
3377            prefix="CLUSTER BY",
3378            into=Cluster,
3379            dialect=dialect,
3380            **opts,
3381        )
3382
3383    def select(
3384        self,
3385        *expressions: t.Optional[ExpOrStr],
3386        append: bool = True,
3387        dialect: DialectType = None,
3388        copy: bool = True,
3389        **opts,
3390    ) -> Select:
3391        return _apply_list_builder(
3392            *expressions,
3393            instance=self,
3394            arg="expressions",
3395            append=append,
3396            dialect=dialect,
3397            into=Expression,
3398            copy=copy,
3399            **opts,
3400        )
3401
3402    def lateral(
3403        self,
3404        *expressions: t.Optional[ExpOrStr],
3405        append: bool = True,
3406        dialect: DialectType = None,
3407        copy: bool = True,
3408        **opts,
3409    ) -> Select:
3410        """
3411        Append to or set the LATERAL expressions.
3412
3413        Example:
3414            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3415            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3416
3417        Args:
3418            *expressions: the SQL code strings to parse.
3419                If an `Expression` instance is passed, it will be used as-is.
3420            append: if `True`, add to any existing expressions.
3421                Otherwise, this resets the expressions.
3422            dialect: the dialect used to parse the input expressions.
3423            copy: if `False`, modify this expression instance in-place.
3424            opts: other options to use to parse the input expressions.
3425
3426        Returns:
3427            The modified Select expression.
3428        """
3429        return _apply_list_builder(
3430            *expressions,
3431            instance=self,
3432            arg="laterals",
3433            append=append,
3434            into=Lateral,
3435            prefix="LATERAL VIEW",
3436            dialect=dialect,
3437            copy=copy,
3438            **opts,
3439        )
3440
3441    def join(
3442        self,
3443        expression: ExpOrStr,
3444        on: t.Optional[ExpOrStr] = None,
3445        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3446        append: bool = True,
3447        join_type: t.Optional[str] = None,
3448        join_alias: t.Optional[Identifier | str] = None,
3449        dialect: DialectType = None,
3450        copy: bool = True,
3451        **opts,
3452    ) -> Select:
3453        """
3454        Append to or set the JOIN expressions.
3455
3456        Example:
3457            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3458            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3459
3460            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3461            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3462
3463            Use `join_type` to change the type of join:
3464
3465            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3466            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3467
3468        Args:
3469            expression: the SQL code string to parse.
3470                If an `Expression` instance is passed, it will be used as-is.
3471            on: optionally specify the join "on" criteria as a SQL string.
3472                If an `Expression` instance is passed, it will be used as-is.
3473            using: optionally specify the join "using" criteria as a SQL string.
3474                If an `Expression` instance is passed, it will be used as-is.
3475            append: if `True`, add to any existing expressions.
3476                Otherwise, this resets the expressions.
3477            join_type: if set, alter the parsed join type.
3478            join_alias: an optional alias for the joined source.
3479            dialect: the dialect used to parse the input expressions.
3480            copy: if `False`, modify this expression instance in-place.
3481            opts: other options to use to parse the input expressions.
3482
3483        Returns:
3484            Select: the modified expression.
3485        """
3486        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3487
3488        try:
3489            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3490        except ParseError:
3491            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3492
3493        join = expression if isinstance(expression, Join) else Join(this=expression)
3494
3495        if isinstance(join.this, Select):
3496            join.this.replace(join.this.subquery())
3497
3498        if join_type:
3499            method: t.Optional[Token]
3500            side: t.Optional[Token]
3501            kind: t.Optional[Token]
3502
3503            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3504
3505            if method:
3506                join.set("method", method.text)
3507            if side:
3508                join.set("side", side.text)
3509            if kind:
3510                join.set("kind", kind.text)
3511
3512        if on:
3513            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3514            join.set("on", on)
3515
3516        if using:
3517            join = _apply_list_builder(
3518                *ensure_list(using),
3519                instance=join,
3520                arg="using",
3521                append=append,
3522                copy=copy,
3523                into=Identifier,
3524                **opts,
3525            )
3526
3527        if join_alias:
3528            join.set("this", alias_(join.this, join_alias, table=True))
3529
3530        return _apply_list_builder(
3531            join,
3532            instance=self,
3533            arg="joins",
3534            append=append,
3535            copy=copy,
3536            **opts,
3537        )
3538
3539    def where(
3540        self,
3541        *expressions: t.Optional[ExpOrStr],
3542        append: bool = True,
3543        dialect: DialectType = None,
3544        copy: bool = True,
3545        **opts,
3546    ) -> Select:
3547        """
3548        Append to or set the WHERE expressions.
3549
3550        Example:
3551            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3552            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3553
3554        Args:
3555            *expressions: the SQL code strings to parse.
3556                If an `Expression` instance is passed, it will be used as-is.
3557                Multiple expressions are combined with an AND operator.
3558            append: if `True`, AND the new expressions to any existing expression.
3559                Otherwise, this resets the expression.
3560            dialect: the dialect used to parse the input expressions.
3561            copy: if `False`, modify this expression instance in-place.
3562            opts: other options to use to parse the input expressions.
3563
3564        Returns:
3565            Select: the modified expression.
3566        """
3567        return _apply_conjunction_builder(
3568            *expressions,
3569            instance=self,
3570            arg="where",
3571            append=append,
3572            into=Where,
3573            dialect=dialect,
3574            copy=copy,
3575            **opts,
3576        )
3577
3578    def having(
3579        self,
3580        *expressions: t.Optional[ExpOrStr],
3581        append: bool = True,
3582        dialect: DialectType = None,
3583        copy: bool = True,
3584        **opts,
3585    ) -> Select:
3586        """
3587        Append to or set the HAVING expressions.
3588
3589        Example:
3590            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3591            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3592
3593        Args:
3594            *expressions: the SQL code strings to parse.
3595                If an `Expression` instance is passed, it will be used as-is.
3596                Multiple expressions are combined with an AND operator.
3597            append: if `True`, AND the new expressions to any existing expression.
3598                Otherwise, this resets the expression.
3599            dialect: the dialect used to parse the input expressions.
3600            copy: if `False`, modify this expression instance in-place.
3601            opts: other options to use to parse the input expressions.
3602
3603        Returns:
3604            The modified Select expression.
3605        """
3606        return _apply_conjunction_builder(
3607            *expressions,
3608            instance=self,
3609            arg="having",
3610            append=append,
3611            into=Having,
3612            dialect=dialect,
3613            copy=copy,
3614            **opts,
3615        )
3616
3617    def window(
3618        self,
3619        *expressions: t.Optional[ExpOrStr],
3620        append: bool = True,
3621        dialect: DialectType = None,
3622        copy: bool = True,
3623        **opts,
3624    ) -> Select:
3625        return _apply_list_builder(
3626            *expressions,
3627            instance=self,
3628            arg="windows",
3629            append=append,
3630            into=Window,
3631            dialect=dialect,
3632            copy=copy,
3633            **opts,
3634        )
3635
3636    def qualify(
3637        self,
3638        *expressions: t.Optional[ExpOrStr],
3639        append: bool = True,
3640        dialect: DialectType = None,
3641        copy: bool = True,
3642        **opts,
3643    ) -> Select:
3644        return _apply_conjunction_builder(
3645            *expressions,
3646            instance=self,
3647            arg="qualify",
3648            append=append,
3649            into=Qualify,
3650            dialect=dialect,
3651            copy=copy,
3652            **opts,
3653        )
3654
3655    def distinct(
3656        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3657    ) -> Select:
3658        """
3659        Set the OFFSET expression.
3660
3661        Example:
3662            >>> Select().from_("tbl").select("x").distinct().sql()
3663            'SELECT DISTINCT x FROM tbl'
3664
3665        Args:
3666            ons: the expressions to distinct on
3667            distinct: whether the Select should be distinct
3668            copy: if `False`, modify this expression instance in-place.
3669
3670        Returns:
3671            Select: the modified expression.
3672        """
3673        instance = maybe_copy(self, copy)
3674        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3675        instance.set("distinct", Distinct(on=on) if distinct else None)
3676        return instance
3677
3678    def ctas(
3679        self,
3680        table: ExpOrStr,
3681        properties: t.Optional[t.Dict] = None,
3682        dialect: DialectType = None,
3683        copy: bool = True,
3684        **opts,
3685    ) -> Create:
3686        """
3687        Convert this expression to a CREATE TABLE AS statement.
3688
3689        Example:
3690            >>> Select().select("*").from_("tbl").ctas("x").sql()
3691            'CREATE TABLE x AS SELECT * FROM tbl'
3692
3693        Args:
3694            table: the SQL code string to parse as the table name.
3695                If another `Expression` instance is passed, it will be used as-is.
3696            properties: an optional mapping of table properties
3697            dialect: the dialect used to parse the input table.
3698            copy: if `False`, modify this expression instance in-place.
3699            opts: other options to use to parse the input table.
3700
3701        Returns:
3702            The new Create expression.
3703        """
3704        instance = maybe_copy(self, copy)
3705        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
3706
3707        properties_expression = None
3708        if properties:
3709            properties_expression = Properties.from_dict(properties)
3710
3711        return Create(
3712            this=table_expression,
3713            kind="TABLE",
3714            expression=instance,
3715            properties=properties_expression,
3716        )
3717
3718    def lock(self, update: bool = True, copy: bool = True) -> Select:
3719        """
3720        Set the locking read mode for this expression.
3721
3722        Examples:
3723            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3724            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3725
3726            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3727            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3728
3729        Args:
3730            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3731            copy: if `False`, modify this expression instance in-place.
3732
3733        Returns:
3734            The modified expression.
3735        """
3736        inst = maybe_copy(self, copy)
3737        inst.set("locks", [Lock(update=update)])
3738
3739        return inst
3740
3741    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3742        """
3743        Set hints for this expression.
3744
3745        Examples:
3746            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3747            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3748
3749        Args:
3750            hints: The SQL code strings to parse as the hints.
3751                If an `Expression` instance is passed, it will be used as-is.
3752            dialect: The dialect used to parse the hints.
3753            copy: If `False`, modify this expression instance in-place.
3754
3755        Returns:
3756            The modified expression.
3757        """
3758        inst = maybe_copy(self, copy)
3759        inst.set(
3760            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3761        )
3762
3763        return inst
3764
3765    @property
3766    def named_selects(self) -> t.List[str]:
3767        return [e.output_name for e in self.expressions if e.alias_or_name]
3768
3769    @property
3770    def is_star(self) -> bool:
3771        return any(expression.is_star for expression in self.expressions)
3772
3773    @property
3774    def selects(self) -> t.List[Expression]:
3775        return self.expressions
3776
3777
3778UNWRAPPED_QUERIES = (Select, SetOperation)
3779
3780
3781class Subquery(DerivedTable, Query):
3782    arg_types = {
3783        "this": True,
3784        "alias": False,
3785        "with": False,
3786        **QUERY_MODIFIERS,
3787    }
3788
3789    def unnest(self):
3790        """Returns the first non subquery."""
3791        expression = self
3792        while isinstance(expression, Subquery):
3793            expression = expression.this
3794        return expression
3795
3796    def unwrap(self) -> Subquery:
3797        expression = self
3798        while expression.same_parent and expression.is_wrapper:
3799            expression = t.cast(Subquery, expression.parent)
3800        return expression
3801
3802    def select(
3803        self,
3804        *expressions: t.Optional[ExpOrStr],
3805        append: bool = True,
3806        dialect: DialectType = None,
3807        copy: bool = True,
3808        **opts,
3809    ) -> Subquery:
3810        this = maybe_copy(self, copy)
3811        this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3812        return this
3813
3814    @property
3815    def is_wrapper(self) -> bool:
3816        """
3817        Whether this Subquery acts as a simple wrapper around another expression.
3818
3819        SELECT * FROM (((SELECT * FROM t)))
3820                      ^
3821                      This corresponds to a "wrapper" Subquery node
3822        """
3823        return all(v is None for k, v in self.args.items() if k != "this")
3824
3825    @property
3826    def is_star(self) -> bool:
3827        return self.this.is_star
3828
3829    @property
3830    def output_name(self) -> str:
3831        return self.alias
3832
3833
3834class TableSample(Expression):
3835    arg_types = {
3836        "this": False,
3837        "expressions": False,
3838        "method": False,
3839        "bucket_numerator": False,
3840        "bucket_denominator": False,
3841        "bucket_field": False,
3842        "percent": False,
3843        "rows": False,
3844        "size": False,
3845        "seed": False,
3846    }
3847
3848
3849class Tag(Expression):
3850    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3851
3852    arg_types = {
3853        "this": False,
3854        "prefix": False,
3855        "postfix": False,
3856    }
3857
3858
3859# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3860# https://duckdb.org/docs/sql/statements/pivot
3861class Pivot(Expression):
3862    arg_types = {
3863        "this": False,
3864        "alias": False,
3865        "expressions": False,
3866        "field": False,
3867        "unpivot": False,
3868        "using": False,
3869        "group": False,
3870        "columns": False,
3871        "include_nulls": False,
3872        "default_on_null": False,
3873    }
3874
3875    @property
3876    def unpivot(self) -> bool:
3877        return bool(self.args.get("unpivot"))
3878
3879
3880class Window(Condition):
3881    arg_types = {
3882        "this": True,
3883        "partition_by": False,
3884        "order": False,
3885        "spec": False,
3886        "alias": False,
3887        "over": False,
3888        "first": False,
3889    }
3890
3891
3892class WindowSpec(Expression):
3893    arg_types = {
3894        "kind": False,
3895        "start": False,
3896        "start_side": False,
3897        "end": False,
3898        "end_side": False,
3899    }
3900
3901
3902class PreWhere(Expression):
3903    pass
3904
3905
3906class Where(Expression):
3907    pass
3908
3909
3910class Star(Expression):
3911    arg_types = {"except": False, "replace": False, "rename": False}
3912
3913    @property
3914    def name(self) -> str:
3915        return "*"
3916
3917    @property
3918    def output_name(self) -> str:
3919        return self.name
3920
3921
3922class Parameter(Condition):
3923    arg_types = {"this": True, "expression": False}
3924
3925
3926class SessionParameter(Condition):
3927    arg_types = {"this": True, "kind": False}
3928
3929
3930class Placeholder(Condition):
3931    arg_types = {"this": False, "kind": False}
3932
3933    @property
3934    def name(self) -> str:
3935        return self.this or "?"
3936
3937
3938class Null(Condition):
3939    arg_types: t.Dict[str, t.Any] = {}
3940
3941    @property
3942    def name(self) -> str:
3943        return "NULL"
3944
3945    def to_py(self) -> Lit[None]:
3946        return None
3947
3948
3949class Boolean(Condition):
3950    def to_py(self) -> bool:
3951        return self.this
3952
3953
3954class DataTypeParam(Expression):
3955    arg_types = {"this": True, "expression": False}
3956
3957    @property
3958    def name(self) -> str:
3959        return self.this.name
3960
3961
3962class DataType(Expression):
3963    arg_types = {
3964        "this": True,
3965        "expressions": False,
3966        "nested": False,
3967        "values": False,
3968        "prefix": False,
3969        "kind": False,
3970    }
3971
3972    class Type(AutoName):
3973        ARRAY = auto()
3974        AGGREGATEFUNCTION = auto()
3975        SIMPLEAGGREGATEFUNCTION = auto()
3976        BIGDECIMAL = auto()
3977        BIGINT = auto()
3978        BIGSERIAL = auto()
3979        BINARY = auto()
3980        BIT = auto()
3981        BOOLEAN = auto()
3982        BPCHAR = auto()
3983        CHAR = auto()
3984        DATE = auto()
3985        DATE32 = auto()
3986        DATEMULTIRANGE = auto()
3987        DATERANGE = auto()
3988        DATETIME = auto()
3989        DATETIME64 = auto()
3990        DECIMAL = auto()
3991        DOUBLE = auto()
3992        ENUM = auto()
3993        ENUM8 = auto()
3994        ENUM16 = auto()
3995        FIXEDSTRING = auto()
3996        FLOAT = auto()
3997        GEOGRAPHY = auto()
3998        GEOMETRY = auto()
3999        HLLSKETCH = auto()
4000        HSTORE = auto()
4001        IMAGE = auto()
4002        INET = auto()
4003        INT = auto()
4004        INT128 = auto()
4005        INT256 = auto()
4006        INT4MULTIRANGE = auto()
4007        INT4RANGE = auto()
4008        INT8MULTIRANGE = auto()
4009        INT8RANGE = auto()
4010        INTERVAL = auto()
4011        IPADDRESS = auto()
4012        IPPREFIX = auto()
4013        IPV4 = auto()
4014        IPV6 = auto()
4015        JSON = auto()
4016        JSONB = auto()
4017        LIST = auto()
4018        LONGBLOB = auto()
4019        LONGTEXT = auto()
4020        LOWCARDINALITY = auto()
4021        MAP = auto()
4022        MEDIUMBLOB = auto()
4023        MEDIUMINT = auto()
4024        MEDIUMTEXT = auto()
4025        MONEY = auto()
4026        NAME = auto()
4027        NCHAR = auto()
4028        NESTED = auto()
4029        NULL = auto()
4030        NULLABLE = auto()
4031        NUMMULTIRANGE = auto()
4032        NUMRANGE = auto()
4033        NVARCHAR = auto()
4034        OBJECT = auto()
4035        ROWVERSION = auto()
4036        SERIAL = auto()
4037        SET = auto()
4038        SMALLINT = auto()
4039        SMALLMONEY = auto()
4040        SMALLSERIAL = auto()
4041        STRUCT = auto()
4042        SUPER = auto()
4043        TEXT = auto()
4044        TINYBLOB = auto()
4045        TINYTEXT = auto()
4046        TIME = auto()
4047        TIMETZ = auto()
4048        TIMESTAMP = auto()
4049        TIMESTAMPNTZ = auto()
4050        TIMESTAMPLTZ = auto()
4051        TIMESTAMPTZ = auto()
4052        TIMESTAMP_S = auto()
4053        TIMESTAMP_MS = auto()
4054        TIMESTAMP_NS = auto()
4055        TINYINT = auto()
4056        TSMULTIRANGE = auto()
4057        TSRANGE = auto()
4058        TSTZMULTIRANGE = auto()
4059        TSTZRANGE = auto()
4060        UBIGINT = auto()
4061        UINT = auto()
4062        UINT128 = auto()
4063        UINT256 = auto()
4064        UMEDIUMINT = auto()
4065        UDECIMAL = auto()
4066        UNIQUEIDENTIFIER = auto()
4067        UNKNOWN = auto()  # Sentinel value, useful for type annotation
4068        USERDEFINED = "USER-DEFINED"
4069        USMALLINT = auto()
4070        UTINYINT = auto()
4071        UUID = auto()
4072        VARBINARY = auto()
4073        VARCHAR = auto()
4074        VARIANT = auto()
4075        VECTOR = auto()
4076        XML = auto()
4077        YEAR = auto()
4078        TDIGEST = auto()
4079
4080    STRUCT_TYPES = {
4081        Type.NESTED,
4082        Type.OBJECT,
4083        Type.STRUCT,
4084    }
4085
4086    NESTED_TYPES = {
4087        *STRUCT_TYPES,
4088        Type.ARRAY,
4089        Type.MAP,
4090    }
4091
4092    TEXT_TYPES = {
4093        Type.CHAR,
4094        Type.NCHAR,
4095        Type.NVARCHAR,
4096        Type.TEXT,
4097        Type.VARCHAR,
4098        Type.NAME,
4099    }
4100
4101    SIGNED_INTEGER_TYPES = {
4102        Type.BIGINT,
4103        Type.INT,
4104        Type.INT128,
4105        Type.INT256,
4106        Type.MEDIUMINT,
4107        Type.SMALLINT,
4108        Type.TINYINT,
4109    }
4110
4111    UNSIGNED_INTEGER_TYPES = {
4112        Type.UBIGINT,
4113        Type.UINT,
4114        Type.UINT128,
4115        Type.UINT256,
4116        Type.UMEDIUMINT,
4117        Type.USMALLINT,
4118        Type.UTINYINT,
4119    }
4120
4121    INTEGER_TYPES = {
4122        *SIGNED_INTEGER_TYPES,
4123        *UNSIGNED_INTEGER_TYPES,
4124        Type.BIT,
4125    }
4126
4127    FLOAT_TYPES = {
4128        Type.DOUBLE,
4129        Type.FLOAT,
4130    }
4131
4132    REAL_TYPES = {
4133        *FLOAT_TYPES,
4134        Type.BIGDECIMAL,
4135        Type.DECIMAL,
4136        Type.MONEY,
4137        Type.SMALLMONEY,
4138        Type.UDECIMAL,
4139    }
4140
4141    NUMERIC_TYPES = {
4142        *INTEGER_TYPES,
4143        *REAL_TYPES,
4144    }
4145
4146    TEMPORAL_TYPES = {
4147        Type.DATE,
4148        Type.DATE32,
4149        Type.DATETIME,
4150        Type.DATETIME64,
4151        Type.TIME,
4152        Type.TIMESTAMP,
4153        Type.TIMESTAMPNTZ,
4154        Type.TIMESTAMPLTZ,
4155        Type.TIMESTAMPTZ,
4156        Type.TIMESTAMP_MS,
4157        Type.TIMESTAMP_NS,
4158        Type.TIMESTAMP_S,
4159        Type.TIMETZ,
4160    }
4161
4162    @classmethod
4163    def build(
4164        cls,
4165        dtype: DATA_TYPE,
4166        dialect: DialectType = None,
4167        udt: bool = False,
4168        copy: bool = True,
4169        **kwargs,
4170    ) -> DataType:
4171        """
4172        Constructs a DataType object.
4173
4174        Args:
4175            dtype: the data type of interest.
4176            dialect: the dialect to use for parsing `dtype`, in case it's a string.
4177            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
4178                DataType, thus creating a user-defined type.
4179            copy: whether to copy the data type.
4180            kwargs: additional arguments to pass in the constructor of DataType.
4181
4182        Returns:
4183            The constructed DataType object.
4184        """
4185        from sqlglot import parse_one
4186
4187        if isinstance(dtype, str):
4188            if dtype.upper() == "UNKNOWN":
4189                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
4190
4191            try:
4192                data_type_exp = parse_one(
4193                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
4194                )
4195            except ParseError:
4196                if udt:
4197                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
4198                raise
4199        elif isinstance(dtype, DataType.Type):
4200            data_type_exp = DataType(this=dtype)
4201        elif isinstance(dtype, DataType):
4202            return maybe_copy(dtype, copy)
4203        else:
4204            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
4205
4206        return DataType(**{**data_type_exp.args, **kwargs})
4207
4208    def is_type(self, *dtypes: DATA_TYPE) -> bool:
4209        """
4210        Checks whether this DataType matches one of the provided data types. Nested types or precision
4211        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
4212
4213        Args:
4214            dtypes: the data types to compare this DataType to.
4215
4216        Returns:
4217            True, if and only if there is a type in `dtypes` which is equal to this DataType.
4218        """
4219        for dtype in dtypes:
4220            other = DataType.build(dtype, copy=False, udt=True)
4221
4222            if (
4223                other.expressions
4224                or self.this == DataType.Type.USERDEFINED
4225                or other.this == DataType.Type.USERDEFINED
4226            ):
4227                matches = self == other
4228            else:
4229                matches = self.this == other.this
4230
4231            if matches:
4232                return True
4233        return False
4234
4235
4236DATA_TYPE = t.Union[str, DataType, DataType.Type]
4237
4238
4239# https://www.postgresql.org/docs/15/datatype-pseudo.html
4240class PseudoType(DataType):
4241    arg_types = {"this": True}
4242
4243
4244# https://www.postgresql.org/docs/15/datatype-oid.html
4245class ObjectIdentifier(DataType):
4246    arg_types = {"this": True}
4247
4248
4249# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
4250class SubqueryPredicate(Predicate):
4251    pass
4252
4253
4254class All(SubqueryPredicate):
4255    pass
4256
4257
4258class Any(SubqueryPredicate):
4259    pass
4260
4261
4262class Exists(SubqueryPredicate):
4263    pass
4264
4265
4266# Commands to interact with the databases or engines. For most of the command
4267# expressions we parse whatever comes after the command's name as a string.
4268class Command(Expression):
4269    arg_types = {"this": True, "expression": False}
4270
4271
4272class Transaction(Expression):
4273    arg_types = {"this": False, "modes": False, "mark": False}
4274
4275
4276class Commit(Expression):
4277    arg_types = {"chain": False, "this": False, "durability": False}
4278
4279
4280class Rollback(Expression):
4281    arg_types = {"savepoint": False, "this": False}
4282
4283
4284class Alter(Expression):
4285    arg_types = {
4286        "this": True,
4287        "kind": True,
4288        "actions": True,
4289        "exists": False,
4290        "only": False,
4291        "options": False,
4292        "cluster": False,
4293    }
4294
4295
4296class AddConstraint(Expression):
4297    arg_types = {"expressions": True}
4298
4299
4300class DropPartition(Expression):
4301    arg_types = {"expressions": True, "exists": False}
4302
4303
4304# https://clickhouse.com/docs/en/sql-reference/statements/alter/partition#replace-partition
4305class ReplacePartition(Expression):
4306    arg_types = {"expression": True, "source": True}
4307
4308
4309# Binary expressions like (ADD a b)
4310class Binary(Condition):
4311    arg_types = {"this": True, "expression": True}
4312
4313    @property
4314    def left(self) -> Expression:
4315        return self.this
4316
4317    @property
4318    def right(self) -> Expression:
4319        return self.expression
4320
4321
4322class Add(Binary):
4323    pass
4324
4325
4326class Connector(Binary):
4327    pass
4328
4329
4330class And(Connector):
4331    pass
4332
4333
4334class Or(Connector):
4335    pass
4336
4337
4338class BitwiseAnd(Binary):
4339    pass
4340
4341
4342class BitwiseLeftShift(Binary):
4343    pass
4344
4345
4346class BitwiseOr(Binary):
4347    pass
4348
4349
4350class BitwiseRightShift(Binary):
4351    pass
4352
4353
4354class BitwiseXor(Binary):
4355    pass
4356
4357
4358class Div(Binary):
4359    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
4360
4361
4362class Overlaps(Binary):
4363    pass
4364
4365
4366class Dot(Binary):
4367    @property
4368    def is_star(self) -> bool:
4369        return self.expression.is_star
4370
4371    @property
4372    def name(self) -> str:
4373        return self.expression.name
4374
4375    @property
4376    def output_name(self) -> str:
4377        return self.name
4378
4379    @classmethod
4380    def build(self, expressions: t.Sequence[Expression]) -> Dot:
4381        """Build a Dot object with a sequence of expressions."""
4382        if len(expressions) < 2:
4383            raise ValueError("Dot requires >= 2 expressions.")
4384
4385        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
4386
4387    @property
4388    def parts(self) -> t.List[Expression]:
4389        """Return the parts of a table / column in order catalog, db, table."""
4390        this, *parts = self.flatten()
4391
4392        parts.reverse()
4393
4394        for arg in COLUMN_PARTS:
4395            part = this.args.get(arg)
4396
4397            if isinstance(part, Expression):
4398                parts.append(part)
4399
4400        parts.reverse()
4401        return parts
4402
4403
4404class DPipe(Binary):
4405    arg_types = {"this": True, "expression": True, "safe": False}
4406
4407
4408class EQ(Binary, Predicate):
4409    pass
4410
4411
4412class NullSafeEQ(Binary, Predicate):
4413    pass
4414
4415
4416class NullSafeNEQ(Binary, Predicate):
4417    pass
4418
4419
4420# Represents e.g. := in DuckDB which is mostly used for setting parameters
4421class PropertyEQ(Binary):
4422    pass
4423
4424
4425class Distance(Binary):
4426    pass
4427
4428
4429class Escape(Binary):
4430    pass
4431
4432
4433class Glob(Binary, Predicate):
4434    pass
4435
4436
4437class GT(Binary, Predicate):
4438    pass
4439
4440
4441class GTE(Binary, Predicate):
4442    pass
4443
4444
4445class ILike(Binary, Predicate):
4446    pass
4447
4448
4449class ILikeAny(Binary, Predicate):
4450    pass
4451
4452
4453class IntDiv(Binary):
4454    pass
4455
4456
4457class Is(Binary, Predicate):
4458    pass
4459
4460
4461class Kwarg(Binary):
4462    """Kwarg in special functions like func(kwarg => y)."""
4463
4464
4465class Like(Binary, Predicate):
4466    pass
4467
4468
4469class LikeAny(Binary, Predicate):
4470    pass
4471
4472
4473class LT(Binary, Predicate):
4474    pass
4475
4476
4477class LTE(Binary, Predicate):
4478    pass
4479
4480
4481class Mod(Binary):
4482    pass
4483
4484
4485class Mul(Binary):
4486    pass
4487
4488
4489class NEQ(Binary, Predicate):
4490    pass
4491
4492
4493# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
4494class Operator(Binary):
4495    arg_types = {"this": True, "operator": True, "expression": True}
4496
4497
4498class SimilarTo(Binary, Predicate):
4499    pass
4500
4501
4502class Slice(Binary):
4503    arg_types = {"this": False, "expression": False}
4504
4505
4506class Sub(Binary):
4507    pass
4508
4509
4510# Unary Expressions
4511# (NOT a)
4512class Unary(Condition):
4513    pass
4514
4515
4516class BitwiseNot(Unary):
4517    pass
4518
4519
4520class Not(Unary):
4521    pass
4522
4523
4524class Paren(Unary):
4525    @property
4526    def output_name(self) -> str:
4527        return self.this.name
4528
4529
4530class Neg(Unary):
4531    def to_py(self) -> int | Decimal:
4532        if self.is_number:
4533            return self.this.to_py() * -1
4534        return super().to_py()
4535
4536
4537class Alias(Expression):
4538    arg_types = {"this": True, "alias": False}
4539
4540    @property
4541    def output_name(self) -> str:
4542        return self.alias
4543
4544
4545# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
4546# other dialects require identifiers. This enables us to transpile between them easily.
4547class PivotAlias(Alias):
4548    pass
4549
4550
4551# Represents Snowflake's ANY [ ORDER BY ... ] syntax
4552# https://docs.snowflake.com/en/sql-reference/constructs/pivot
4553class PivotAny(Expression):
4554    arg_types = {"this": False}
4555
4556
4557class Aliases(Expression):
4558    arg_types = {"this": True, "expressions": True}
4559
4560    @property
4561    def aliases(self):
4562        return self.expressions
4563
4564
4565# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
4566class AtIndex(Expression):
4567    arg_types = {"this": True, "expression": True}
4568
4569
4570class AtTimeZone(Expression):
4571    arg_types = {"this": True, "zone": True}
4572
4573
4574class FromTimeZone(Expression):
4575    arg_types = {"this": True, "zone": True}
4576
4577
4578class Between(Predicate):
4579    arg_types = {"this": True, "low": True, "high": True}
4580
4581
4582class Bracket(Condition):
4583    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
4584    arg_types = {
4585        "this": True,
4586        "expressions": True,
4587        "offset": False,
4588        "safe": False,
4589        "returns_list_for_maps": False,
4590    }
4591
4592    @property
4593    def output_name(self) -> str:
4594        if len(self.expressions) == 1:
4595            return self.expressions[0].output_name
4596
4597        return super().output_name
4598
4599
4600class Distinct(Expression):
4601    arg_types = {"expressions": False, "on": False}
4602
4603
4604class In(Predicate):
4605    arg_types = {
4606        "this": True,
4607        "expressions": False,
4608        "query": False,
4609        "unnest": False,
4610        "field": False,
4611        "is_global": False,
4612    }
4613
4614
4615# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4616class ForIn(Expression):
4617    arg_types = {"this": True, "expression": True}
4618
4619
4620class TimeUnit(Expression):
4621    """Automatically converts unit arg into a var."""
4622
4623    arg_types = {"unit": False}
4624
4625    UNABBREVIATED_UNIT_NAME = {
4626        "D": "DAY",
4627        "H": "HOUR",
4628        "M": "MINUTE",
4629        "MS": "MILLISECOND",
4630        "NS": "NANOSECOND",
4631        "Q": "QUARTER",
4632        "S": "SECOND",
4633        "US": "MICROSECOND",
4634        "W": "WEEK",
4635        "Y": "YEAR",
4636    }
4637
4638    VAR_LIKE = (Column, Literal, Var)
4639
4640    def __init__(self, **args):
4641        unit = args.get("unit")
4642        if isinstance(unit, self.VAR_LIKE):
4643            args["unit"] = Var(
4644                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
4645            )
4646        elif isinstance(unit, Week):
4647            unit.set("this", Var(this=unit.this.name.upper()))
4648
4649        super().__init__(**args)
4650
4651    @property
4652    def unit(self) -> t.Optional[Var | IntervalSpan]:
4653        return self.args.get("unit")
4654
4655
4656class IntervalOp(TimeUnit):
4657    arg_types = {"unit": True, "expression": True}
4658
4659    def interval(self):
4660        return Interval(
4661            this=self.expression.copy(),
4662            unit=self.unit.copy(),
4663        )
4664
4665
4666# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4667# https://trino.io/docs/current/language/types.html#interval-day-to-second
4668# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4669class IntervalSpan(DataType):
4670    arg_types = {"this": True, "expression": True}
4671
4672
4673class Interval(TimeUnit):
4674    arg_types = {"this": False, "unit": False}
4675
4676
4677class IgnoreNulls(Expression):
4678    pass
4679
4680
4681class RespectNulls(Expression):
4682    pass
4683
4684
4685# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls#max_min_clause
4686class HavingMax(Expression):
4687    arg_types = {"this": True, "expression": True, "max": True}
4688
4689
4690# Functions
4691class Func(Condition):
4692    """
4693    The base class for all function expressions.
4694
4695    Attributes:
4696        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4697            treated as a variable length argument and the argument's value will be stored as a list.
4698        _sql_names (list): the SQL name (1st item in the list) and aliases (subsequent items) for this
4699            function expression. These values are used to map this node to a name during parsing as
4700            well as to provide the function's name during SQL string generation. By default the SQL
4701            name is set to the expression's class name transformed to snake case.
4702    """
4703
4704    is_var_len_args = False
4705
4706    @classmethod
4707    def from_arg_list(cls, args):
4708        if cls.is_var_len_args:
4709            all_arg_keys = list(cls.arg_types)
4710            # If this function supports variable length argument treat the last argument as such.
4711            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4712            num_non_var = len(non_var_len_arg_keys)
4713
4714            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4715            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4716        else:
4717            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4718
4719        return cls(**args_dict)
4720
4721    @classmethod
4722    def sql_names(cls):
4723        if cls is Func:
4724            raise NotImplementedError(
4725                "SQL name is only supported by concrete function implementations"
4726            )
4727        if "_sql_names" not in cls.__dict__:
4728            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4729        return cls._sql_names
4730
4731    @classmethod
4732    def sql_name(cls):
4733        return cls.sql_names()[0]
4734
4735    @classmethod
4736    def default_parser_mappings(cls):
4737        return {name: cls.from_arg_list for name in cls.sql_names()}
4738
4739
4740class AggFunc(Func):
4741    pass
4742
4743
4744class ParameterizedAgg(AggFunc):
4745    arg_types = {"this": True, "expressions": True, "params": True}
4746
4747
4748class Abs(Func):
4749    pass
4750
4751
4752class ArgMax(AggFunc):
4753    arg_types = {"this": True, "expression": True, "count": False}
4754    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4755
4756
4757class ArgMin(AggFunc):
4758    arg_types = {"this": True, "expression": True, "count": False}
4759    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4760
4761
4762class ApproxTopK(AggFunc):
4763    arg_types = {"this": True, "expression": False, "counters": False}
4764
4765
4766class Flatten(Func):
4767    pass
4768
4769
4770# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4771class Transform(Func):
4772    arg_types = {"this": True, "expression": True}
4773
4774
4775class Anonymous(Func):
4776    arg_types = {"this": True, "expressions": False}
4777    is_var_len_args = True
4778
4779    @property
4780    def name(self) -> str:
4781        return self.this if isinstance(self.this, str) else self.this.name
4782
4783
4784class AnonymousAggFunc(AggFunc):
4785    arg_types = {"this": True, "expressions": False}
4786    is_var_len_args = True
4787
4788
4789# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
4790class CombinedAggFunc(AnonymousAggFunc):
4791    arg_types = {"this": True, "expressions": False, "parts": True}
4792
4793
4794class CombinedParameterizedAgg(ParameterizedAgg):
4795    arg_types = {"this": True, "expressions": True, "params": True, "parts": True}
4796
4797
4798# https://docs.snowflake.com/en/sql-reference/functions/hll
4799# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4800class Hll(AggFunc):
4801    arg_types = {"this": True, "expressions": False}
4802    is_var_len_args = True
4803
4804
4805class ApproxDistinct(AggFunc):
4806    arg_types = {"this": True, "accuracy": False}
4807    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4808
4809
4810class Array(Func):
4811    arg_types = {"expressions": False, "bracket_notation": False}
4812    is_var_len_args = True
4813
4814
4815# https://docs.snowflake.com/en/sql-reference/functions/to_array
4816class ToArray(Func):
4817    pass
4818
4819
4820# https://materialize.com/docs/sql/types/list/
4821class List(Func):
4822    arg_types = {"expressions": False}
4823    is_var_len_args = True
4824
4825
4826# String pad, kind True -> LPAD, False -> RPAD
4827class Pad(Func):
4828    arg_types = {"this": True, "expression": True, "fill_pattern": False, "is_left": True}
4829
4830
4831# https://docs.snowflake.com/en/sql-reference/functions/to_char
4832# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
4833class ToChar(Func):
4834    arg_types = {"this": True, "format": False, "nlsparam": False}
4835
4836
4837# https://docs.snowflake.com/en/sql-reference/functions/to_decimal
4838# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_NUMBER.html
4839class ToNumber(Func):
4840    arg_types = {
4841        "this": True,
4842        "format": False,
4843        "nlsparam": False,
4844        "precision": False,
4845        "scale": False,
4846    }
4847
4848
4849# https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#syntax
4850class Convert(Func):
4851    arg_types = {"this": True, "expression": True, "style": False}
4852
4853
4854class ConvertTimezone(Func):
4855    arg_types = {"source_tz": False, "target_tz": True, "timestamp": True}
4856
4857
4858class GenerateSeries(Func):
4859    arg_types = {"start": True, "end": True, "step": False, "is_end_exclusive": False}
4860
4861
4862# Postgres' GENERATE_SERIES function returns a row set, i.e. it implicitly explodes when it's
4863# used in a projection, so this expression is a helper that facilitates transpilation to other
4864# dialects. For example, we'd generate UNNEST(GENERATE_SERIES(...)) in DuckDB
4865class ExplodingGenerateSeries(GenerateSeries):
4866    pass
4867
4868
4869class ArrayAgg(AggFunc):
4870    pass
4871
4872
4873class ArrayUniqueAgg(AggFunc):
4874    pass
4875
4876
4877class ArrayAll(Func):
4878    arg_types = {"this": True, "expression": True}
4879
4880
4881# Represents Python's `any(f(x) for x in array)`, where `array` is `this` and `f` is `expression`
4882class ArrayAny(Func):
4883    arg_types = {"this": True, "expression": True}
4884
4885
4886class ArrayConcat(Func):
4887    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4888    arg_types = {"this": True, "expressions": False}
4889    is_var_len_args = True
4890
4891
4892class ArrayConstructCompact(Func):
4893    arg_types = {"expressions": True}
4894    is_var_len_args = True
4895
4896
4897class ArrayContains(Binary, Func):
4898    _sql_names = ["ARRAY_CONTAINS", "ARRAY_HAS"]
4899
4900
4901class ArrayContainsAll(Binary, Func):
4902    _sql_names = ["ARRAY_CONTAINS_ALL", "ARRAY_HAS_ALL"]
4903
4904
4905class ArrayFilter(Func):
4906    arg_types = {"this": True, "expression": True}
4907    _sql_names = ["FILTER", "ARRAY_FILTER"]
4908
4909
4910class ArrayToString(Func):
4911    arg_types = {"this": True, "expression": True, "null": False}
4912    _sql_names = ["ARRAY_TO_STRING", "ARRAY_JOIN"]
4913
4914
4915class StringToArray(Func):
4916    arg_types = {"this": True, "expression": True, "null": False}
4917    _sql_names = ["STRING_TO_ARRAY", "SPLIT_BY_STRING"]
4918
4919
4920class ArrayOverlaps(Binary, Func):
4921    pass
4922
4923
4924class ArraySize(Func):
4925    arg_types = {"this": True, "expression": False}
4926    _sql_names = ["ARRAY_SIZE", "ARRAY_LENGTH"]
4927
4928
4929class ArraySort(Func):
4930    arg_types = {"this": True, "expression": False}
4931
4932
4933class ArraySum(Func):
4934    arg_types = {"this": True, "expression": False}
4935
4936
4937class ArrayUnionAgg(AggFunc):
4938    pass
4939
4940
4941class Avg(AggFunc):
4942    pass
4943
4944
4945class AnyValue(AggFunc):
4946    pass
4947
4948
4949class Lag(AggFunc):
4950    arg_types = {"this": True, "offset": False, "default": False}
4951
4952
4953class Lead(AggFunc):
4954    arg_types = {"this": True, "offset": False, "default": False}
4955
4956
4957# some dialects have a distinction between first and first_value, usually first is an aggregate func
4958# and first_value is a window func
4959class First(AggFunc):
4960    pass
4961
4962
4963class Last(AggFunc):
4964    pass
4965
4966
4967class FirstValue(AggFunc):
4968    pass
4969
4970
4971class LastValue(AggFunc):
4972    pass
4973
4974
4975class NthValue(AggFunc):
4976    arg_types = {"this": True, "offset": True}
4977
4978
4979class Case(Func):
4980    arg_types = {"this": False, "ifs": True, "default": False}
4981
4982    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4983        instance = maybe_copy(self, copy)
4984        instance.append(
4985            "ifs",
4986            If(
4987                this=maybe_parse(condition, copy=copy, **opts),
4988                true=maybe_parse(then, copy=copy, **opts),
4989            ),
4990        )
4991        return instance
4992
4993    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4994        instance = maybe_copy(self, copy)
4995        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4996        return instance
4997
4998
4999class Cast(Func):
5000    arg_types = {
5001        "this": True,
5002        "to": True,
5003        "format": False,
5004        "safe": False,
5005        "action": False,
5006    }
5007
5008    @property
5009    def name(self) -> str:
5010        return self.this.name
5011
5012    @property
5013    def to(self) -> DataType:
5014        return self.args["to"]
5015
5016    @property
5017    def output_name(self) -> str:
5018        return self.name
5019
5020    def is_type(self, *dtypes: DATA_TYPE) -> bool:
5021        """
5022        Checks whether this Cast's DataType matches one of the provided data types. Nested types
5023        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
5024        array<int> != array<float>.
5025
5026        Args:
5027            dtypes: the data types to compare this Cast's DataType to.
5028
5029        Returns:
5030            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
5031        """
5032        return self.to.is_type(*dtypes)
5033
5034
5035class TryCast(Cast):
5036    pass
5037
5038
5039class Try(Func):
5040    pass
5041
5042
5043class CastToStrType(Func):
5044    arg_types = {"this": True, "to": True}
5045
5046
5047class Collate(Binary, Func):
5048    pass
5049
5050
5051class Ceil(Func):
5052    arg_types = {"this": True, "decimals": False}
5053    _sql_names = ["CEIL", "CEILING"]
5054
5055
5056class Coalesce(Func):
5057    arg_types = {"this": True, "expressions": False}
5058    is_var_len_args = True
5059    _sql_names = ["COALESCE", "IFNULL", "NVL"]
5060
5061
5062class Chr(Func):
5063    arg_types = {"this": True, "charset": False, "expressions": False}
5064    is_var_len_args = True
5065    _sql_names = ["CHR", "CHAR"]
5066
5067
5068class Concat(Func):
5069    arg_types = {"expressions": True, "safe": False, "coalesce": False}
5070    is_var_len_args = True
5071
5072
5073class ConcatWs(Concat):
5074    _sql_names = ["CONCAT_WS"]
5075
5076
5077# https://docs.oracle.com/cd/B13789_01/server.101/b10759/operators004.htm#i1035022
5078class ConnectByRoot(Func):
5079    pass
5080
5081
5082class Count(AggFunc):
5083    arg_types = {"this": False, "expressions": False}
5084    is_var_len_args = True
5085
5086
5087class CountIf(AggFunc):
5088    _sql_names = ["COUNT_IF", "COUNTIF"]
5089
5090
5091# cube root
5092class Cbrt(Func):
5093    pass
5094
5095
5096class CurrentDate(Func):
5097    arg_types = {"this": False}
5098
5099
5100class CurrentDatetime(Func):
5101    arg_types = {"this": False}
5102
5103
5104class CurrentTime(Func):
5105    arg_types = {"this": False}
5106
5107
5108class CurrentTimestamp(Func):
5109    arg_types = {"this": False, "transaction": False}
5110
5111
5112class CurrentUser(Func):
5113    arg_types = {"this": False}
5114
5115
5116class DateAdd(Func, IntervalOp):
5117    arg_types = {"this": True, "expression": True, "unit": False}
5118
5119
5120class DateSub(Func, IntervalOp):
5121    arg_types = {"this": True, "expression": True, "unit": False}
5122
5123
5124class DateDiff(Func, TimeUnit):
5125    _sql_names = ["DATEDIFF", "DATE_DIFF"]
5126    arg_types = {"this": True, "expression": True, "unit": False}
5127
5128
5129class DateTrunc(Func):
5130    arg_types = {"unit": True, "this": True, "zone": False}
5131
5132    def __init__(self, **args):
5133        unit = args.get("unit")
5134        if isinstance(unit, TimeUnit.VAR_LIKE):
5135            args["unit"] = Literal.string(
5136                (TimeUnit.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
5137            )
5138        elif isinstance(unit, Week):
5139            unit.set("this", Literal.string(unit.this.name.upper()))
5140
5141        super().__init__(**args)
5142
5143    @property
5144    def unit(self) -> Expression:
5145        return self.args["unit"]
5146
5147
5148# https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#datetime
5149# expression can either be time_expr or time_zone
5150class Datetime(Func):
5151    arg_types = {"this": True, "expression": False}
5152
5153
5154class DatetimeAdd(Func, IntervalOp):
5155    arg_types = {"this": True, "expression": True, "unit": False}
5156
5157
5158class DatetimeSub(Func, IntervalOp):
5159    arg_types = {"this": True, "expression": True, "unit": False}
5160
5161
5162class DatetimeDiff(Func, TimeUnit):
5163    arg_types = {"this": True, "expression": True, "unit": False}
5164
5165
5166class DatetimeTrunc(Func, TimeUnit):
5167    arg_types = {"this": True, "unit": True, "zone": False}
5168
5169
5170class DayOfWeek(Func):
5171    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
5172
5173
5174class DayOfMonth(Func):
5175    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
5176
5177
5178class DayOfYear(Func):
5179    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
5180
5181
5182class ToDays(Func):
5183    pass
5184
5185
5186class WeekOfYear(Func):
5187    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
5188
5189
5190class MonthsBetween(Func):
5191    arg_types = {"this": True, "expression": True, "roundoff": False}
5192
5193
5194class LastDay(Func, TimeUnit):
5195    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
5196    arg_types = {"this": True, "unit": False}
5197
5198
5199class Extract(Func):
5200    arg_types = {"this": True, "expression": True}
5201
5202
5203class Timestamp(Func):
5204    arg_types = {"this": False, "zone": False, "with_tz": False}
5205
5206
5207class TimestampAdd(Func, TimeUnit):
5208    arg_types = {"this": True, "expression": True, "unit": False}
5209
5210
5211class TimestampSub(Func, TimeUnit):
5212    arg_types = {"this": True, "expression": True, "unit": False}
5213
5214
5215class TimestampDiff(Func, TimeUnit):
5216    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
5217    arg_types = {"this": True, "expression": True, "unit": False}
5218
5219
5220class TimestampTrunc(Func, TimeUnit):
5221    arg_types = {"this": True, "unit": True, "zone": False}
5222
5223
5224class TimeAdd(Func, TimeUnit):
5225    arg_types = {"this": True, "expression": True, "unit": False}
5226
5227
5228class TimeSub(Func, TimeUnit):
5229    arg_types = {"this": True, "expression": True, "unit": False}
5230
5231
5232class TimeDiff(Func, TimeUnit):
5233    arg_types = {"this": True, "expression": True, "unit": False}
5234
5235
5236class TimeTrunc(Func, TimeUnit):
5237    arg_types = {"this": True, "unit": True, "zone": False}
5238
5239
5240class DateFromParts(Func):
5241    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
5242    arg_types = {"year": True, "month": True, "day": True}
5243
5244
5245class TimeFromParts(Func):
5246    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
5247    arg_types = {
5248        "hour": True,
5249        "min": True,
5250        "sec": True,
5251        "nano": False,
5252        "fractions": False,
5253        "precision": False,
5254    }
5255
5256
5257class DateStrToDate(Func):
5258    pass
5259
5260
5261class DateToDateStr(Func):
5262    pass
5263
5264
5265class DateToDi(Func):
5266    pass
5267
5268
5269# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
5270class Date(Func):
5271    arg_types = {"this": False, "zone": False, "expressions": False}
5272    is_var_len_args = True
5273
5274
5275class Day(Func):
5276    pass
5277
5278
5279class Decode(Func):
5280    arg_types = {"this": True, "charset": True, "replace": False}
5281
5282
5283class DiToDate(Func):
5284    pass
5285
5286
5287class Encode(Func):
5288    arg_types = {"this": True, "charset": True}
5289
5290
5291class Exp(Func):
5292    pass
5293
5294
5295# https://docs.snowflake.com/en/sql-reference/functions/flatten
5296class Explode(Func):
5297    arg_types = {"this": True, "expressions": False}
5298    is_var_len_args = True
5299
5300
5301class ExplodeOuter(Explode):
5302    pass
5303
5304
5305class Posexplode(Explode):
5306    pass
5307
5308
5309class PosexplodeOuter(Posexplode, ExplodeOuter):
5310    pass
5311
5312
5313class Unnest(Func, UDTF):
5314    arg_types = {
5315        "expressions": True,
5316        "alias": False,
5317        "offset": False,
5318    }
5319
5320    @property
5321    def selects(self) -> t.List[Expression]:
5322        columns = super().selects
5323        offset = self.args.get("offset")
5324        if offset:
5325            columns = columns + [to_identifier("offset") if offset is True else offset]
5326        return columns
5327
5328
5329class Floor(Func):
5330    arg_types = {"this": True, "decimals": False}
5331
5332
5333class FromBase64(Func):
5334    pass
5335
5336
5337class ToBase64(Func):
5338    pass
5339
5340
5341class GapFill(Func):
5342    arg_types = {
5343        "this": True,
5344        "ts_column": True,
5345        "bucket_width": True,
5346        "partitioning_columns": False,
5347        "value_columns": False,
5348        "origin": False,
5349        "ignore_nulls": False,
5350    }
5351
5352
5353# https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions#generate_date_array
5354class GenerateDateArray(Func):
5355    arg_types = {"start": True, "end": True, "step": False}
5356
5357
5358# https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions#generate_timestamp_array
5359class GenerateTimestampArray(Func):
5360    arg_types = {"start": True, "end": True, "step": True}
5361
5362
5363class Greatest(Func):
5364    arg_types = {"this": True, "expressions": False}
5365    is_var_len_args = True
5366
5367
5368class GroupConcat(AggFunc):
5369    arg_types = {"this": True, "separator": False}
5370
5371
5372class Hex(Func):
5373    pass
5374
5375
5376class LowerHex(Hex):
5377    pass
5378
5379
5380class Xor(Connector, Func):
5381    arg_types = {"this": False, "expression": False, "expressions": False}
5382
5383
5384class If(Func):
5385    arg_types = {"this": True, "true": True, "false": False}
5386    _sql_names = ["IF", "IIF"]
5387
5388
5389class Nullif(Func):
5390    arg_types = {"this": True, "expression": True}
5391
5392
5393class Initcap(Func):
5394    arg_types = {"this": True, "expression": False}
5395
5396
5397class IsNan(Func):
5398    _sql_names = ["IS_NAN", "ISNAN"]
5399
5400
5401class IsInf(Func):
5402    _sql_names = ["IS_INF", "ISINF"]
5403
5404
5405class JSONPath(Expression):
5406    arg_types = {"expressions": True}
5407
5408    @property
5409    def output_name(self) -> str:
5410        last_segment = self.expressions[-1].this
5411        return last_segment if isinstance(last_segment, str) else ""
5412
5413
5414class JSONPathPart(Expression):
5415    arg_types = {}
5416
5417
5418class JSONPathFilter(JSONPathPart):
5419    arg_types = {"this": True}
5420
5421
5422class JSONPathKey(JSONPathPart):
5423    arg_types = {"this": True}
5424
5425
5426class JSONPathRecursive(JSONPathPart):
5427    arg_types = {"this": False}
5428
5429
5430class JSONPathRoot(JSONPathPart):
5431    pass
5432
5433
5434class JSONPathScript(JSONPathPart):
5435    arg_types = {"this": True}
5436
5437
5438class JSONPathSlice(JSONPathPart):
5439    arg_types = {"start": False, "end": False, "step": False}
5440
5441
5442class JSONPathSelector(JSONPathPart):
5443    arg_types = {"this": True}
5444
5445
5446class JSONPathSubscript(JSONPathPart):
5447    arg_types = {"this": True}
5448
5449
5450class JSONPathUnion(JSONPathPart):
5451    arg_types = {"expressions": True}
5452
5453
5454class JSONPathWildcard(JSONPathPart):
5455    pass
5456
5457
5458class FormatJson(Expression):
5459    pass
5460
5461
5462class JSONKeyValue(Expression):
5463    arg_types = {"this": True, "expression": True}
5464
5465
5466class JSONObject(Func):
5467    arg_types = {
5468        "expressions": False,
5469        "null_handling": False,
5470        "unique_keys": False,
5471        "return_type": False,
5472        "encoding": False,
5473    }
5474
5475
5476class JSONObjectAgg(AggFunc):
5477    arg_types = {
5478        "expressions": False,
5479        "null_handling": False,
5480        "unique_keys": False,
5481        "return_type": False,
5482        "encoding": False,
5483    }
5484
5485
5486# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
5487class JSONArray(Func):
5488    arg_types = {
5489        "expressions": True,
5490        "null_handling": False,
5491        "return_type": False,
5492        "strict": False,
5493    }
5494
5495
5496# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
5497class JSONArrayAgg(Func):
5498    arg_types = {
5499        "this": True,
5500        "order": False,
5501        "null_handling": False,
5502        "return_type": False,
5503        "strict": False,
5504    }
5505
5506
5507# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5508# Note: parsing of JSON column definitions is currently incomplete.
5509class JSONColumnDef(Expression):
5510    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
5511
5512
5513class JSONSchema(Expression):
5514    arg_types = {"expressions": True}
5515
5516
5517# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
5518class JSONTable(Func):
5519    arg_types = {
5520        "this": True,
5521        "schema": True,
5522        "path": False,
5523        "error_handling": False,
5524        "empty_handling": False,
5525    }
5526
5527
5528# https://docs.snowflake.com/en/sql-reference/functions/object_insert
5529class ObjectInsert(Func):
5530    arg_types = {
5531        "this": True,
5532        "key": True,
5533        "value": True,
5534        "update_flag": False,
5535    }
5536
5537
5538class OpenJSONColumnDef(Expression):
5539    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
5540
5541
5542class OpenJSON(Func):
5543    arg_types = {"this": True, "path": False, "expressions": False}
5544
5545
5546class JSONBContains(Binary, Func):
5547    _sql_names = ["JSONB_CONTAINS"]
5548
5549
5550class JSONExtract(Binary, Func):
5551    arg_types = {
5552        "this": True,
5553        "expression": True,
5554        "only_json_types": False,
5555        "expressions": False,
5556        "variant_extract": False,
5557    }
5558    _sql_names = ["JSON_EXTRACT"]
5559    is_var_len_args = True
5560
5561    @property
5562    def output_name(self) -> str:
5563        return self.expression.output_name if not self.expressions else ""
5564
5565
5566class JSONExtractScalar(Binary, Func):
5567    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
5568    _sql_names = ["JSON_EXTRACT_SCALAR"]
5569    is_var_len_args = True
5570
5571    @property
5572    def output_name(self) -> str:
5573        return self.expression.output_name
5574
5575
5576class JSONBExtract(Binary, Func):
5577    _sql_names = ["JSONB_EXTRACT"]
5578
5579
5580class JSONBExtractScalar(Binary, Func):
5581    _sql_names = ["JSONB_EXTRACT_SCALAR"]
5582
5583
5584class JSONFormat(Func):
5585    arg_types = {"this": False, "options": False}
5586    _sql_names = ["JSON_FORMAT"]
5587
5588
5589# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
5590class JSONArrayContains(Binary, Predicate, Func):
5591    _sql_names = ["JSON_ARRAY_CONTAINS"]
5592
5593
5594class ParseJSON(Func):
5595    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
5596    # Snowflake also has TRY_PARSE_JSON, which is represented using `safe`
5597    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
5598    arg_types = {"this": True, "expression": False, "safe": False}
5599
5600
5601class Least(Func):
5602    arg_types = {"this": True, "expressions": False}
5603    is_var_len_args = True
5604
5605
5606class Left(Func):
5607    arg_types = {"this": True, "expression": True}
5608
5609
5610class Right(Func):
5611    arg_types = {"this": True, "expression": True}
5612
5613
5614class Length(Func):
5615    arg_types = {"this": True, "binary": False}
5616    _sql_names = ["LENGTH", "LEN"]
5617
5618
5619class Levenshtein(Func):
5620    arg_types = {
5621        "this": True,
5622        "expression": False,
5623        "ins_cost": False,
5624        "del_cost": False,
5625        "sub_cost": False,
5626    }
5627
5628
5629class Ln(Func):
5630    pass
5631
5632
5633class Log(Func):
5634    arg_types = {"this": True, "expression": False}
5635
5636
5637class LogicalOr(AggFunc):
5638    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
5639
5640
5641class LogicalAnd(AggFunc):
5642    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
5643
5644
5645class Lower(Func):
5646    _sql_names = ["LOWER", "LCASE"]
5647
5648
5649class Map(Func):
5650    arg_types = {"keys": False, "values": False}
5651
5652    @property
5653    def keys(self) -> t.List[Expression]:
5654        keys = self.args.get("keys")
5655        return keys.expressions if keys else []
5656
5657    @property
5658    def values(self) -> t.List[Expression]:
5659        values = self.args.get("values")
5660        return values.expressions if values else []
5661
5662
5663# Represents the MAP {...} syntax in DuckDB - basically convert a struct to a MAP
5664class ToMap(Func):
5665    pass
5666
5667
5668class MapFromEntries(Func):
5669    pass
5670
5671
5672# https://learn.microsoft.com/en-us/sql/t-sql/language-elements/scope-resolution-operator-transact-sql?view=sql-server-ver16
5673class ScopeResolution(Expression):
5674    arg_types = {"this": False, "expression": True}
5675
5676
5677class Stream(Expression):
5678    pass
5679
5680
5681class StarMap(Func):
5682    pass
5683
5684
5685class VarMap(Func):
5686    arg_types = {"keys": True, "values": True}
5687    is_var_len_args = True
5688
5689    @property
5690    def keys(self) -> t.List[Expression]:
5691        return self.args["keys"].expressions
5692
5693    @property
5694    def values(self) -> t.List[Expression]:
5695        return self.args["values"].expressions
5696
5697
5698# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5699class MatchAgainst(Func):
5700    arg_types = {"this": True, "expressions": True, "modifier": False}
5701
5702
5703class Max(AggFunc):
5704    arg_types = {"this": True, "expressions": False}
5705    is_var_len_args = True
5706
5707
5708class MD5(Func):
5709    _sql_names = ["MD5"]
5710
5711
5712# Represents the variant of the MD5 function that returns a binary value
5713class MD5Digest(Func):
5714    _sql_names = ["MD5_DIGEST"]
5715
5716
5717class Min(AggFunc):
5718    arg_types = {"this": True, "expressions": False}
5719    is_var_len_args = True
5720
5721
5722class Month(Func):
5723    pass
5724
5725
5726class AddMonths(Func):
5727    arg_types = {"this": True, "expression": True}
5728
5729
5730class Nvl2(Func):
5731    arg_types = {"this": True, "true": True, "false": False}
5732
5733
5734# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
5735class Predict(Func):
5736    arg_types = {"this": True, "expression": True, "params_struct": False}
5737
5738
5739class Pow(Binary, Func):
5740    _sql_names = ["POWER", "POW"]
5741
5742
5743class PercentileCont(AggFunc):
5744    arg_types = {"this": True, "expression": False}
5745
5746
5747class PercentileDisc(AggFunc):
5748    arg_types = {"this": True, "expression": False}
5749
5750
5751class Quantile(AggFunc):
5752    arg_types = {"this": True, "quantile": True}
5753
5754
5755class ApproxQuantile(Quantile):
5756    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
5757
5758
5759class Quarter(Func):
5760    pass
5761
5762
5763# https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/SQL-Functions-Expressions-and-Predicates/Arithmetic-Trigonometric-Hyperbolic-Operators/Functions/RANDOM/RANDOM-Function-Syntax
5764# teradata lower and upper bounds
5765class Rand(Func):
5766    _sql_names = ["RAND", "RANDOM"]
5767    arg_types = {"this": False, "lower": False, "upper": False}
5768
5769
5770class Randn(Func):
5771    arg_types = {"this": False}
5772
5773
5774class RangeN(Func):
5775    arg_types = {"this": True, "expressions": True, "each": False}
5776
5777
5778class ReadCSV(Func):
5779    _sql_names = ["READ_CSV"]
5780    is_var_len_args = True
5781    arg_types = {"this": True, "expressions": False}
5782
5783
5784class Reduce(Func):
5785    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
5786
5787
5788class RegexpExtract(Func):
5789    arg_types = {
5790        "this": True,
5791        "expression": True,
5792        "position": False,
5793        "occurrence": False,
5794        "parameters": False,
5795        "group": False,
5796    }
5797
5798
5799class RegexpReplace(Func):
5800    arg_types = {
5801        "this": True,
5802        "expression": True,
5803        "replacement": False,
5804        "position": False,
5805        "occurrence": False,
5806        "modifiers": False,
5807    }
5808
5809
5810class RegexpLike(Binary, Func):
5811    arg_types = {"this": True, "expression": True, "flag": False}
5812
5813
5814class RegexpILike(Binary, Func):
5815    arg_types = {"this": True, "expression": True, "flag": False}
5816
5817
5818# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5819# limit is the number of times a pattern is applied
5820class RegexpSplit(Func):
5821    arg_types = {"this": True, "expression": True, "limit": False}
5822
5823
5824class Repeat(Func):
5825    arg_types = {"this": True, "times": True}
5826
5827
5828# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
5829# tsql third argument function == trunctaion if not 0
5830class Round(Func):
5831    arg_types = {"this": True, "decimals": False, "truncate": False}
5832
5833
5834class RowNumber(Func):
5835    arg_types: t.Dict[str, t.Any] = {}
5836
5837
5838class SafeDivide(Func):
5839    arg_types = {"this": True, "expression": True}
5840
5841
5842class SHA(Func):
5843    _sql_names = ["SHA", "SHA1"]
5844
5845
5846class SHA2(Func):
5847    _sql_names = ["SHA2"]
5848    arg_types = {"this": True, "length": False}
5849
5850
5851class Sign(Func):
5852    _sql_names = ["SIGN", "SIGNUM"]
5853
5854
5855class SortArray(Func):
5856    arg_types = {"this": True, "asc": False}
5857
5858
5859class Split(Func):
5860    arg_types = {"this": True, "expression": True, "limit": False}
5861
5862
5863# Start may be omitted in the case of postgres
5864# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5865class Substring(Func):
5866    arg_types = {"this": True, "start": False, "length": False}
5867
5868
5869class StandardHash(Func):
5870    arg_types = {"this": True, "expression": False}
5871
5872
5873class StartsWith(Func):
5874    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5875    arg_types = {"this": True, "expression": True}
5876
5877
5878class StrPosition(Func):
5879    arg_types = {
5880        "this": True,
5881        "substr": True,
5882        "position": False,
5883        "instance": False,
5884    }
5885
5886
5887class StrToDate(Func):
5888    arg_types = {"this": True, "format": False, "safe": False}
5889
5890
5891class StrToTime(Func):
5892    arg_types = {"this": True, "format": True, "zone": False, "safe": False}
5893
5894
5895# Spark allows unix_timestamp()
5896# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5897class StrToUnix(Func):
5898    arg_types = {"this": False, "format": False}
5899
5900
5901# https://prestodb.io/docs/current/functions/string.html
5902# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5903class StrToMap(Func):
5904    arg_types = {
5905        "this": True,
5906        "pair_delim": False,
5907        "key_value_delim": False,
5908        "duplicate_resolution_callback": False,
5909    }
5910
5911
5912class NumberToStr(Func):
5913    arg_types = {"this": True, "format": True, "culture": False}
5914
5915
5916class FromBase(Func):
5917    arg_types = {"this": True, "expression": True}
5918
5919
5920class Struct(Func):
5921    arg_types = {"expressions": False}
5922    is_var_len_args = True
5923
5924
5925class StructExtract(Func):
5926    arg_types = {"this": True, "expression": True}
5927
5928
5929# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5930# https://docs.snowflake.com/en/sql-reference/functions/insert
5931class Stuff(Func):
5932    _sql_names = ["STUFF", "INSERT"]
5933    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5934
5935
5936class Sum(AggFunc):
5937    pass
5938
5939
5940class Sqrt(Func):
5941    pass
5942
5943
5944class Stddev(AggFunc):
5945    _sql_names = ["STDDEV", "STDEV"]
5946
5947
5948class StddevPop(AggFunc):
5949    pass
5950
5951
5952class StddevSamp(AggFunc):
5953    pass
5954
5955
5956# https://cloud.google.com/bigquery/docs/reference/standard-sql/time_functions#time
5957class Time(Func):
5958    arg_types = {"this": False, "zone": False}
5959
5960
5961class TimeToStr(Func):
5962    arg_types = {"this": True, "format": True, "culture": False, "timezone": False}
5963
5964
5965class TimeToTimeStr(Func):
5966    pass
5967
5968
5969class TimeToUnix(Func):
5970    pass
5971
5972
5973class TimeStrToDate(Func):
5974    pass
5975
5976
5977class TimeStrToTime(Func):
5978    pass
5979
5980
5981class TimeStrToUnix(Func):
5982    pass
5983
5984
5985class Trim(Func):
5986    arg_types = {
5987        "this": True,
5988        "expression": False,
5989        "position": False,
5990        "collation": False,
5991    }
5992
5993
5994class TsOrDsAdd(Func, TimeUnit):
5995    # return_type is used to correctly cast the arguments of this expression when transpiling it
5996    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
5997
5998    @property
5999    def return_type(self) -> DataType:
6000        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
6001
6002
6003class TsOrDsDiff(Func, TimeUnit):
6004    arg_types = {"this": True, "expression": True, "unit": False}
6005
6006
6007class TsOrDsToDateStr(Func):
6008    pass
6009
6010
6011class TsOrDsToDate(Func):
6012    arg_types = {"this": True, "format": False, "safe": False}
6013
6014
6015class TsOrDsToTime(Func):
6016    pass
6017
6018
6019class TsOrDsToTimestamp(Func):
6020    pass
6021
6022
6023class TsOrDiToDi(Func):
6024    pass
6025
6026
6027class Unhex(Func):
6028    pass
6029
6030
6031# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
6032class UnixDate(Func):
6033    pass
6034
6035
6036class UnixToStr(Func):
6037    arg_types = {"this": True, "format": False}
6038
6039
6040# https://prestodb.io/docs/current/functions/datetime.html
6041# presto has weird zone/hours/minutes
6042class UnixToTime(Func):
6043    arg_types = {
6044        "this": True,
6045        "scale": False,
6046        "zone": False,
6047        "hours": False,
6048        "minutes": False,
6049        "format": False,
6050    }
6051
6052    SECONDS = Literal.number(0)
6053    DECIS = Literal.number(1)
6054    CENTIS = Literal.number(2)
6055    MILLIS = Literal.number(3)
6056    DECIMILLIS = Literal.number(4)
6057    CENTIMILLIS = Literal.number(5)
6058    MICROS = Literal.number(6)
6059    DECIMICROS = Literal.number(7)
6060    CENTIMICROS = Literal.number(8)
6061    NANOS = Literal.number(9)
6062
6063
6064class UnixToTimeStr(Func):
6065    pass
6066
6067
6068class TimestampFromParts(Func):
6069    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
6070    arg_types = {
6071        "year": True,
6072        "month": True,
6073        "day": True,
6074        "hour": True,
6075        "min": True,
6076        "sec": True,
6077        "nano": False,
6078        "zone": False,
6079        "milli": False,
6080    }
6081
6082
6083class Upper(Func):
6084    _sql_names = ["UPPER", "UCASE"]
6085
6086
6087class Corr(Binary, AggFunc):
6088    pass
6089
6090
6091class Variance(AggFunc):
6092    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
6093
6094
6095class VariancePop(AggFunc):
6096    _sql_names = ["VARIANCE_POP", "VAR_POP"]
6097
6098
6099class CovarSamp(Binary, AggFunc):
6100    pass
6101
6102
6103class CovarPop(Binary, AggFunc):
6104    pass
6105
6106
6107class Week(Func):
6108    arg_types = {"this": True, "mode": False}
6109
6110
6111class XMLTable(Func):
6112    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
6113
6114
6115class Year(Func):
6116    pass
6117
6118
6119class Use(Expression):
6120    arg_types = {"this": True, "kind": False}
6121
6122
6123class Merge(Expression):
6124    arg_types = {
6125        "this": True,
6126        "using": True,
6127        "on": True,
6128        "expressions": True,
6129        "with": False,
6130    }
6131
6132
6133class When(Func):
6134    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
6135
6136
6137# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
6138# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
6139class NextValueFor(Func):
6140    arg_types = {"this": True, "order": False}
6141
6142
6143# Refers to a trailing semi-colon. This is only used to preserve trailing comments
6144# select 1; -- my comment
6145class Semicolon(Expression):
6146    arg_types = {}
6147
6148
6149def _norm_arg(arg):
6150    return arg.lower() if type(arg) is str else arg
6151
6152
6153ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
6154FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
6155
6156JSON_PATH_PARTS = subclasses(__name__, JSONPathPart, (JSONPathPart,))
6157
6158PERCENTILES = (PercentileCont, PercentileDisc)
6159
6160
6161# Helpers
6162@t.overload
6163def maybe_parse(
6164    sql_or_expression: ExpOrStr,
6165    *,
6166    into: t.Type[E],
6167    dialect: DialectType = None,
6168    prefix: t.Optional[str] = None,
6169    copy: bool = False,
6170    **opts,
6171) -> E: ...
6172
6173
6174@t.overload
6175def maybe_parse(
6176    sql_or_expression: str | E,
6177    *,
6178    into: t.Optional[IntoType] = None,
6179    dialect: DialectType = None,
6180    prefix: t.Optional[str] = None,
6181    copy: bool = False,
6182    **opts,
6183) -> E: ...
6184
6185
6186def maybe_parse(
6187    sql_or_expression: ExpOrStr,
6188    *,
6189    into: t.Optional[IntoType] = None,
6190    dialect: DialectType = None,
6191    prefix: t.Optional[str] = None,
6192    copy: bool = False,
6193    **opts,
6194) -> Expression:
6195    """Gracefully handle a possible string or expression.
6196
6197    Example:
6198        >>> maybe_parse("1")
6199        Literal(this=1, is_string=False)
6200        >>> maybe_parse(to_identifier("x"))
6201        Identifier(this=x, quoted=False)
6202
6203    Args:
6204        sql_or_expression: the SQL code string or an expression
6205        into: the SQLGlot Expression to parse into
6206        dialect: the dialect used to parse the input expressions (in the case that an
6207            input expression is a SQL string).
6208        prefix: a string to prefix the sql with before it gets parsed
6209            (automatically includes a space)
6210        copy: whether to copy the expression.
6211        **opts: other options to use to parse the input expressions (again, in the case
6212            that an input expression is a SQL string).
6213
6214    Returns:
6215        Expression: the parsed or given expression.
6216    """
6217    if isinstance(sql_or_expression, Expression):
6218        if copy:
6219            return sql_or_expression.copy()
6220        return sql_or_expression
6221
6222    if sql_or_expression is None:
6223        raise ParseError("SQL cannot be None")
6224
6225    import sqlglot
6226
6227    sql = str(sql_or_expression)
6228    if prefix:
6229        sql = f"{prefix} {sql}"
6230
6231    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
6232
6233
6234@t.overload
6235def maybe_copy(instance: None, copy: bool = True) -> None: ...
6236
6237
6238@t.overload
6239def maybe_copy(instance: E, copy: bool = True) -> E: ...
6240
6241
6242def maybe_copy(instance, copy=True):
6243    return instance.copy() if copy and instance else instance
6244
6245
6246def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
6247    """Generate a textual representation of an Expression tree"""
6248    indent = "\n" + ("  " * (level + 1))
6249    delim = f",{indent}"
6250
6251    if isinstance(node, Expression):
6252        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
6253
6254        if (node.type or verbose) and not isinstance(node, DataType):
6255            args["_type"] = node.type
6256        if node.comments or verbose:
6257            args["_comments"] = node.comments
6258
6259        if verbose:
6260            args["_id"] = id(node)
6261
6262        # Inline leaves for a more compact representation
6263        if node.is_leaf():
6264            indent = ""
6265            delim = ", "
6266
6267        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
6268        return f"{node.__class__.__name__}({indent}{items})"
6269
6270    if isinstance(node, list):
6271        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
6272        items = f"{indent}{items}" if items else ""
6273        return f"[{items}]"
6274
6275    # Indent multiline strings to match the current level
6276    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
6277
6278
6279def _is_wrong_expression(expression, into):
6280    return isinstance(expression, Expression) and not isinstance(expression, into)
6281
6282
6283def _apply_builder(
6284    expression,
6285    instance,
6286    arg,
6287    copy=True,
6288    prefix=None,
6289    into=None,
6290    dialect=None,
6291    into_arg="this",
6292    **opts,
6293):
6294    if _is_wrong_expression(expression, into):
6295        expression = into(**{into_arg: expression})
6296    instance = maybe_copy(instance, copy)
6297    expression = maybe_parse(
6298        sql_or_expression=expression,
6299        prefix=prefix,
6300        into=into,
6301        dialect=dialect,
6302        **opts,
6303    )
6304    instance.set(arg, expression)
6305    return instance
6306
6307
6308def _apply_child_list_builder(
6309    *expressions,
6310    instance,
6311    arg,
6312    append=True,
6313    copy=True,
6314    prefix=None,
6315    into=None,
6316    dialect=None,
6317    properties=None,
6318    **opts,
6319):
6320    instance = maybe_copy(instance, copy)
6321    parsed = []
6322    properties = {} if properties is None else properties
6323
6324    for expression in expressions:
6325        if expression is not None:
6326            if _is_wrong_expression(expression, into):
6327                expression = into(expressions=[expression])
6328
6329            expression = maybe_parse(
6330                expression,
6331                into=into,
6332                dialect=dialect,
6333                prefix=prefix,
6334                **opts,
6335            )
6336            for k, v in expression.args.items():
6337                if k == "expressions":
6338                    parsed.extend(v)
6339                else:
6340                    properties[k] = v
6341
6342    existing = instance.args.get(arg)
6343    if append and existing:
6344        parsed = existing.expressions + parsed
6345
6346    child = into(expressions=parsed)
6347    for k, v in properties.items():
6348        child.set(k, v)
6349    instance.set(arg, child)
6350
6351    return instance
6352
6353
6354def _apply_list_builder(
6355    *expressions,
6356    instance,
6357    arg,
6358    append=True,
6359    copy=True,
6360    prefix=None,
6361    into=None,
6362    dialect=None,
6363    **opts,
6364):
6365    inst = maybe_copy(instance, copy)
6366
6367    expressions = [
6368        maybe_parse(
6369            sql_or_expression=expression,
6370            into=into,
6371            prefix=prefix,
6372            dialect=dialect,
6373            **opts,
6374        )
6375        for expression in expressions
6376        if expression is not None
6377    ]
6378
6379    existing_expressions = inst.args.get(arg)
6380    if append and existing_expressions:
6381        expressions = existing_expressions + expressions
6382
6383    inst.set(arg, expressions)
6384    return inst
6385
6386
6387def _apply_conjunction_builder(
6388    *expressions,
6389    instance,
6390    arg,
6391    into=None,
6392    append=True,
6393    copy=True,
6394    dialect=None,
6395    **opts,
6396):
6397    expressions = [exp for exp in expressions if exp is not None and exp != ""]
6398    if not expressions:
6399        return instance
6400
6401    inst = maybe_copy(instance, copy)
6402
6403    existing = inst.args.get(arg)
6404    if append and existing is not None:
6405        expressions = [existing.this if into else existing] + list(expressions)
6406
6407    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
6408
6409    inst.set(arg, into(this=node) if into else node)
6410    return inst
6411
6412
6413def _apply_cte_builder(
6414    instance: E,
6415    alias: ExpOrStr,
6416    as_: ExpOrStr,
6417    recursive: t.Optional[bool] = None,
6418    append: bool = True,
6419    dialect: DialectType = None,
6420    copy: bool = True,
6421    **opts,
6422) -> E:
6423    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
6424    as_expression = maybe_parse(as_, dialect=dialect, **opts)
6425    cte = CTE(this=as_expression, alias=alias_expression)
6426    return _apply_child_list_builder(
6427        cte,
6428        instance=instance,
6429        arg="with",
6430        append=append,
6431        copy=copy,
6432        into=With,
6433        properties={"recursive": recursive or False},
6434    )
6435
6436
6437def _combine(
6438    expressions: t.Sequence[t.Optional[ExpOrStr]],
6439    operator: t.Type[Connector],
6440    dialect: DialectType = None,
6441    copy: bool = True,
6442    **opts,
6443) -> Expression:
6444    conditions = [
6445        condition(expression, dialect=dialect, copy=copy, **opts)
6446        for expression in expressions
6447        if expression is not None
6448    ]
6449
6450    this, *rest = conditions
6451    if rest:
6452        this = _wrap(this, Connector)
6453    for expression in rest:
6454        this = operator(this=this, expression=_wrap(expression, Connector))
6455
6456    return this
6457
6458
6459def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
6460    return Paren(this=expression) if isinstance(expression, kind) else expression
6461
6462
6463def union(
6464    left: ExpOrStr,
6465    right: ExpOrStr,
6466    distinct: bool = True,
6467    dialect: DialectType = None,
6468    copy: bool = True,
6469    **opts,
6470) -> Union:
6471    """
6472    Initializes a syntax tree from one UNION expression.
6473
6474    Example:
6475        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
6476        'SELECT * FROM foo UNION SELECT * FROM bla'
6477
6478    Args:
6479        left: the SQL code string corresponding to the left-hand side.
6480            If an `Expression` instance is passed, it will be used as-is.
6481        right: the SQL code string corresponding to the right-hand side.
6482            If an `Expression` instance is passed, it will be used as-is.
6483        distinct: set the DISTINCT flag if and only if this is true.
6484        dialect: the dialect used to parse the input expression.
6485        copy: whether to copy the expression.
6486        opts: other options to use to parse the input expressions.
6487
6488    Returns:
6489        The new Union instance.
6490    """
6491    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6492    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6493
6494    return Union(this=left, expression=right, distinct=distinct)
6495
6496
6497def intersect(
6498    left: ExpOrStr,
6499    right: ExpOrStr,
6500    distinct: bool = True,
6501    dialect: DialectType = None,
6502    copy: bool = True,
6503    **opts,
6504) -> Intersect:
6505    """
6506    Initializes a syntax tree from one INTERSECT expression.
6507
6508    Example:
6509        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
6510        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
6511
6512    Args:
6513        left: the SQL code string corresponding to the left-hand side.
6514            If an `Expression` instance is passed, it will be used as-is.
6515        right: the SQL code string corresponding to the right-hand side.
6516            If an `Expression` instance is passed, it will be used as-is.
6517        distinct: set the DISTINCT flag if and only if this is true.
6518        dialect: the dialect used to parse the input expression.
6519        copy: whether to copy the expression.
6520        opts: other options to use to parse the input expressions.
6521
6522    Returns:
6523        The new Intersect instance.
6524    """
6525    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6526    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6527
6528    return Intersect(this=left, expression=right, distinct=distinct)
6529
6530
6531def except_(
6532    left: ExpOrStr,
6533    right: ExpOrStr,
6534    distinct: bool = True,
6535    dialect: DialectType = None,
6536    copy: bool = True,
6537    **opts,
6538) -> Except:
6539    """
6540    Initializes a syntax tree from one EXCEPT expression.
6541
6542    Example:
6543        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
6544        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
6545
6546    Args:
6547        left: the SQL code string corresponding to the left-hand side.
6548            If an `Expression` instance is passed, it will be used as-is.
6549        right: the SQL code string corresponding to the right-hand side.
6550            If an `Expression` instance is passed, it will be used as-is.
6551        distinct: set the DISTINCT flag if and only if this is true.
6552        dialect: the dialect used to parse the input expression.
6553        copy: whether to copy the expression.
6554        opts: other options to use to parse the input expressions.
6555
6556    Returns:
6557        The new Except instance.
6558    """
6559    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
6560    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
6561
6562    return Except(this=left, expression=right, distinct=distinct)
6563
6564
6565def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6566    """
6567    Initializes a syntax tree from one or multiple SELECT expressions.
6568
6569    Example:
6570        >>> select("col1", "col2").from_("tbl").sql()
6571        'SELECT col1, col2 FROM tbl'
6572
6573    Args:
6574        *expressions: the SQL code string to parse as the expressions of a
6575            SELECT statement. If an Expression instance is passed, this is used as-is.
6576        dialect: the dialect used to parse the input expressions (in the case that an
6577            input expression is a SQL string).
6578        **opts: other options to use to parse the input expressions (again, in the case
6579            that an input expression is a SQL string).
6580
6581    Returns:
6582        Select: the syntax tree for the SELECT statement.
6583    """
6584    return Select().select(*expressions, dialect=dialect, **opts)
6585
6586
6587def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
6588    """
6589    Initializes a syntax tree from a FROM expression.
6590
6591    Example:
6592        >>> from_("tbl").select("col1", "col2").sql()
6593        'SELECT col1, col2 FROM tbl'
6594
6595    Args:
6596        *expression: the SQL code string to parse as the FROM expressions of a
6597            SELECT statement. If an Expression instance is passed, this is used as-is.
6598        dialect: the dialect used to parse the input expression (in the case that the
6599            input expression is a SQL string).
6600        **opts: other options to use to parse the input expressions (again, in the case
6601            that the input expression is a SQL string).
6602
6603    Returns:
6604        Select: the syntax tree for the SELECT statement.
6605    """
6606    return Select().from_(expression, dialect=dialect, **opts)
6607
6608
6609def update(
6610    table: str | Table,
6611    properties: dict,
6612    where: t.Optional[ExpOrStr] = None,
6613    from_: t.Optional[ExpOrStr] = None,
6614    dialect: DialectType = None,
6615    **opts,
6616) -> Update:
6617    """
6618    Creates an update statement.
6619
6620    Example:
6621        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
6622        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
6623
6624    Args:
6625        *properties: dictionary of properties to set which are
6626            auto converted to sql objects eg None -> NULL
6627        where: sql conditional parsed into a WHERE statement
6628        from_: sql statement parsed into a FROM statement
6629        dialect: the dialect used to parse the input expressions.
6630        **opts: other options to use to parse the input expressions.
6631
6632    Returns:
6633        Update: the syntax tree for the UPDATE statement.
6634    """
6635    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
6636    update_expr.set(
6637        "expressions",
6638        [
6639            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
6640            for k, v in properties.items()
6641        ],
6642    )
6643    if from_:
6644        update_expr.set(
6645            "from",
6646            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
6647        )
6648    if isinstance(where, Condition):
6649        where = Where(this=where)
6650    if where:
6651        update_expr.set(
6652            "where",
6653            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
6654        )
6655    return update_expr
6656
6657
6658def delete(
6659    table: ExpOrStr,
6660    where: t.Optional[ExpOrStr] = None,
6661    returning: t.Optional[ExpOrStr] = None,
6662    dialect: DialectType = None,
6663    **opts,
6664) -> Delete:
6665    """
6666    Builds a delete statement.
6667
6668    Example:
6669        >>> delete("my_table", where="id > 1").sql()
6670        'DELETE FROM my_table WHERE id > 1'
6671
6672    Args:
6673        where: sql conditional parsed into a WHERE statement
6674        returning: sql conditional parsed into a RETURNING statement
6675        dialect: the dialect used to parse the input expressions.
6676        **opts: other options to use to parse the input expressions.
6677
6678    Returns:
6679        Delete: the syntax tree for the DELETE statement.
6680    """
6681    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
6682    if where:
6683        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
6684    if returning:
6685        delete_expr = t.cast(
6686            Delete, delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
6687        )
6688    return delete_expr
6689
6690
6691def insert(
6692    expression: ExpOrStr,
6693    into: ExpOrStr,
6694    columns: t.Optional[t.Sequence[str | Identifier]] = None,
6695    overwrite: t.Optional[bool] = None,
6696    returning: t.Optional[ExpOrStr] = None,
6697    dialect: DialectType = None,
6698    copy: bool = True,
6699    **opts,
6700) -> Insert:
6701    """
6702    Builds an INSERT statement.
6703
6704    Example:
6705        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
6706        'INSERT INTO tbl VALUES (1, 2, 3)'
6707
6708    Args:
6709        expression: the sql string or expression of the INSERT statement
6710        into: the tbl to insert data to.
6711        columns: optionally the table's column names.
6712        overwrite: whether to INSERT OVERWRITE or not.
6713        returning: sql conditional parsed into a RETURNING statement
6714        dialect: the dialect used to parse the input expressions.
6715        copy: whether to copy the expression.
6716        **opts: other options to use to parse the input expressions.
6717
6718    Returns:
6719        Insert: the syntax tree for the INSERT statement.
6720    """
6721    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6722    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
6723
6724    if columns:
6725        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
6726
6727    insert = Insert(this=this, expression=expr, overwrite=overwrite)
6728
6729    if returning:
6730        insert = t.cast(Insert, insert.returning(returning, dialect=dialect, copy=False, **opts))
6731
6732    return insert
6733
6734
6735def condition(
6736    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
6737) -> Condition:
6738    """
6739    Initialize a logical condition expression.
6740
6741    Example:
6742        >>> condition("x=1").sql()
6743        'x = 1'
6744
6745        This is helpful for composing larger logical syntax trees:
6746        >>> where = condition("x=1")
6747        >>> where = where.and_("y=1")
6748        >>> Select().from_("tbl").select("*").where(where).sql()
6749        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
6750
6751    Args:
6752        *expression: the SQL code string to parse.
6753            If an Expression instance is passed, this is used as-is.
6754        dialect: the dialect used to parse the input expression (in the case that the
6755            input expression is a SQL string).
6756        copy: Whether to copy `expression` (only applies to expressions).
6757        **opts: other options to use to parse the input expressions (again, in the case
6758            that the input expression is a SQL string).
6759
6760    Returns:
6761        The new Condition instance
6762    """
6763    return maybe_parse(
6764        expression,
6765        into=Condition,
6766        dialect=dialect,
6767        copy=copy,
6768        **opts,
6769    )
6770
6771
6772def and_(
6773    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6774) -> Condition:
6775    """
6776    Combine multiple conditions with an AND logical operator.
6777
6778    Example:
6779        >>> and_("x=1", and_("y=1", "z=1")).sql()
6780        'x = 1 AND (y = 1 AND z = 1)'
6781
6782    Args:
6783        *expressions: the SQL code strings to parse.
6784            If an Expression instance is passed, this is used as-is.
6785        dialect: the dialect used to parse the input expression.
6786        copy: whether to copy `expressions` (only applies to Expressions).
6787        **opts: other options to use to parse the input expressions.
6788
6789    Returns:
6790        The new condition
6791    """
6792    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
6793
6794
6795def or_(
6796    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6797) -> Condition:
6798    """
6799    Combine multiple conditions with an OR logical operator.
6800
6801    Example:
6802        >>> or_("x=1", or_("y=1", "z=1")).sql()
6803        'x = 1 OR (y = 1 OR z = 1)'
6804
6805    Args:
6806        *expressions: the SQL code strings to parse.
6807            If an Expression instance is passed, this is used as-is.
6808        dialect: the dialect used to parse the input expression.
6809        copy: whether to copy `expressions` (only applies to Expressions).
6810        **opts: other options to use to parse the input expressions.
6811
6812    Returns:
6813        The new condition
6814    """
6815    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
6816
6817
6818def xor(
6819    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
6820) -> Condition:
6821    """
6822    Combine multiple conditions with an XOR logical operator.
6823
6824    Example:
6825        >>> xor("x=1", xor("y=1", "z=1")).sql()
6826        'x = 1 XOR (y = 1 XOR z = 1)'
6827
6828    Args:
6829        *expressions: the SQL code strings to parse.
6830            If an Expression instance is passed, this is used as-is.
6831        dialect: the dialect used to parse the input expression.
6832        copy: whether to copy `expressions` (only applies to Expressions).
6833        **opts: other options to use to parse the input expressions.
6834
6835    Returns:
6836        The new condition
6837    """
6838    return t.cast(Condition, _combine(expressions, Xor, dialect, copy=copy, **opts))
6839
6840
6841def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
6842    """
6843    Wrap a condition with a NOT operator.
6844
6845    Example:
6846        >>> not_("this_suit='black'").sql()
6847        "NOT this_suit = 'black'"
6848
6849    Args:
6850        expression: the SQL code string to parse.
6851            If an Expression instance is passed, this is used as-is.
6852        dialect: the dialect used to parse the input expression.
6853        copy: whether to copy the expression or not.
6854        **opts: other options to use to parse the input expressions.
6855
6856    Returns:
6857        The new condition.
6858    """
6859    this = condition(
6860        expression,
6861        dialect=dialect,
6862        copy=copy,
6863        **opts,
6864    )
6865    return Not(this=_wrap(this, Connector))
6866
6867
6868def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
6869    """
6870    Wrap an expression in parentheses.
6871
6872    Example:
6873        >>> paren("5 + 3").sql()
6874        '(5 + 3)'
6875
6876    Args:
6877        expression: the SQL code string to parse.
6878            If an Expression instance is passed, this is used as-is.
6879        copy: whether to copy the expression or not.
6880
6881    Returns:
6882        The wrapped expression.
6883    """
6884    return Paren(this=maybe_parse(expression, copy=copy))
6885
6886
6887SAFE_IDENTIFIER_RE: t.Pattern[str] = re.compile(r"^[_a-zA-Z][\w]*$")
6888
6889
6890@t.overload
6891def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None: ...
6892
6893
6894@t.overload
6895def to_identifier(
6896    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
6897) -> Identifier: ...
6898
6899
6900def to_identifier(name, quoted=None, copy=True):
6901    """Builds an identifier.
6902
6903    Args:
6904        name: The name to turn into an identifier.
6905        quoted: Whether to force quote the identifier.
6906        copy: Whether to copy name if it's an Identifier.
6907
6908    Returns:
6909        The identifier ast node.
6910    """
6911
6912    if name is None:
6913        return None
6914
6915    if isinstance(name, Identifier):
6916        identifier = maybe_copy(name, copy)
6917    elif isinstance(name, str):
6918        identifier = Identifier(
6919            this=name,
6920            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
6921        )
6922    else:
6923        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
6924    return identifier
6925
6926
6927def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
6928    """
6929    Parses a given string into an identifier.
6930
6931    Args:
6932        name: The name to parse into an identifier.
6933        dialect: The dialect to parse against.
6934
6935    Returns:
6936        The identifier ast node.
6937    """
6938    try:
6939        expression = maybe_parse(name, dialect=dialect, into=Identifier)
6940    except (ParseError, TokenError):
6941        expression = to_identifier(name)
6942
6943    return expression
6944
6945
6946INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
6947
6948
6949def to_interval(interval: str | Literal) -> Interval:
6950    """Builds an interval expression from a string like '1 day' or '5 months'."""
6951    if isinstance(interval, Literal):
6952        if not interval.is_string:
6953            raise ValueError("Invalid interval string.")
6954
6955        interval = interval.this
6956
6957    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
6958
6959    if not interval_parts:
6960        raise ValueError("Invalid interval string.")
6961
6962    return Interval(
6963        this=Literal.string(interval_parts.group(1)),
6964        unit=Var(this=interval_parts.group(2).upper()),
6965    )
6966
6967
6968def to_table(
6969    sql_path: str | Table, dialect: DialectType = None, copy: bool = True, **kwargs
6970) -> Table:
6971    """
6972    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6973    If a table is passed in then that table is returned.
6974
6975    Args:
6976        sql_path: a `[catalog].[schema].[table]` string.
6977        dialect: the source dialect according to which the table name will be parsed.
6978        copy: Whether to copy a table if it is passed in.
6979        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6980
6981    Returns:
6982        A table expression.
6983    """
6984    if isinstance(sql_path, Table):
6985        return maybe_copy(sql_path, copy=copy)
6986
6987    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6988
6989    for k, v in kwargs.items():
6990        table.set(k, v)
6991
6992    return table
6993
6994
6995def to_column(
6996    sql_path: str | Column,
6997    quoted: t.Optional[bool] = None,
6998    dialect: DialectType = None,
6999    copy: bool = True,
7000    **kwargs,
7001) -> Column:
7002    """
7003    Create a column from a `[table].[column]` sql path. Table is optional.
7004    If a column is passed in then that column is returned.
7005
7006    Args:
7007        sql_path: a `[table].[column]` string.
7008        quoted: Whether or not to force quote identifiers.
7009        dialect: the source dialect according to which the column name will be parsed.
7010        copy: Whether to copy a column if it is passed in.
7011        kwargs: the kwargs to instantiate the resulting `Column` expression with.
7012
7013    Returns:
7014        A column expression.
7015    """
7016    if isinstance(sql_path, Column):
7017        return maybe_copy(sql_path, copy=copy)
7018
7019    try:
7020        col = maybe_parse(sql_path, into=Column, dialect=dialect)
7021    except ParseError:
7022        return column(*reversed(sql_path.split(".")), quoted=quoted, **kwargs)
7023
7024    for k, v in kwargs.items():
7025        col.set(k, v)
7026
7027    if quoted:
7028        for i in col.find_all(Identifier):
7029            i.set("quoted", True)
7030
7031    return col
7032
7033
7034def alias_(
7035    expression: ExpOrStr,
7036    alias: t.Optional[str | Identifier],
7037    table: bool | t.Sequence[str | Identifier] = False,
7038    quoted: t.Optional[bool] = None,
7039    dialect: DialectType = None,
7040    copy: bool = True,
7041    **opts,
7042):
7043    """Create an Alias expression.
7044
7045    Example:
7046        >>> alias_('foo', 'bar').sql()
7047        'foo AS bar'
7048
7049        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
7050        '(SELECT 1, 2) AS bar(a, b)'
7051
7052    Args:
7053        expression: the SQL code strings to parse.
7054            If an Expression instance is passed, this is used as-is.
7055        alias: the alias name to use. If the name has
7056            special characters it is quoted.
7057        table: Whether to create a table alias, can also be a list of columns.
7058        quoted: whether to quote the alias
7059        dialect: the dialect used to parse the input expression.
7060        copy: Whether to copy the expression.
7061        **opts: other options to use to parse the input expressions.
7062
7063    Returns:
7064        Alias: the aliased expression
7065    """
7066    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
7067    alias = to_identifier(alias, quoted=quoted)
7068
7069    if table:
7070        table_alias = TableAlias(this=alias)
7071        exp.set("alias", table_alias)
7072
7073        if not isinstance(table, bool):
7074            for column in table:
7075                table_alias.append("columns", to_identifier(column, quoted=quoted))
7076
7077        return exp
7078
7079    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
7080    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
7081    # for the complete Window expression.
7082    #
7083    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
7084
7085    if "alias" in exp.arg_types and not isinstance(exp, Window):
7086        exp.set("alias", alias)
7087        return exp
7088    return Alias(this=exp, alias=alias)
7089
7090
7091def subquery(
7092    expression: ExpOrStr,
7093    alias: t.Optional[Identifier | str] = None,
7094    dialect: DialectType = None,
7095    **opts,
7096) -> Select:
7097    """
7098    Build a subquery expression that's selected from.
7099
7100    Example:
7101        >>> subquery('select x from tbl', 'bar').select('x').sql()
7102        'SELECT x FROM (SELECT x FROM tbl) AS bar'
7103
7104    Args:
7105        expression: the SQL code strings to parse.
7106            If an Expression instance is passed, this is used as-is.
7107        alias: the alias name to use.
7108        dialect: the dialect used to parse the input expression.
7109        **opts: other options to use to parse the input expressions.
7110
7111    Returns:
7112        A new Select instance with the subquery expression included.
7113    """
7114
7115    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias, **opts)
7116    return Select().from_(expression, dialect=dialect, **opts)
7117
7118
7119@t.overload
7120def column(
7121    col: str | Identifier,
7122    table: t.Optional[str | Identifier] = None,
7123    db: t.Optional[str | Identifier] = None,
7124    catalog: t.Optional[str | Identifier] = None,
7125    *,
7126    fields: t.Collection[t.Union[str, Identifier]],
7127    quoted: t.Optional[bool] = None,
7128    copy: bool = True,
7129) -> Dot:
7130    pass
7131
7132
7133@t.overload
7134def column(
7135    col: str | Identifier,
7136    table: t.Optional[str | Identifier] = None,
7137    db: t.Optional[str | Identifier] = None,
7138    catalog: t.Optional[str | Identifier] = None,
7139    *,
7140    fields: Lit[None] = None,
7141    quoted: t.Optional[bool] = None,
7142    copy: bool = True,
7143) -> Column:
7144    pass
7145
7146
7147def column(
7148    col,
7149    table=None,
7150    db=None,
7151    catalog=None,
7152    *,
7153    fields=None,
7154    quoted=None,
7155    copy=True,
7156):
7157    """
7158    Build a Column.
7159
7160    Args:
7161        col: Column name.
7162        table: Table name.
7163        db: Database name.
7164        catalog: Catalog name.
7165        fields: Additional fields using dots.
7166        quoted: Whether to force quotes on the column's identifiers.
7167        copy: Whether to copy identifiers if passed in.
7168
7169    Returns:
7170        The new Column instance.
7171    """
7172    this = Column(
7173        this=to_identifier(col, quoted=quoted, copy=copy),
7174        table=to_identifier(table, quoted=quoted, copy=copy),
7175        db=to_identifier(db, quoted=quoted, copy=copy),
7176        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
7177    )
7178
7179    if fields:
7180        this = Dot.build(
7181            (this, *(to_identifier(field, quoted=quoted, copy=copy) for field in fields))
7182        )
7183    return this
7184
7185
7186def cast(expression: ExpOrStr, to: DATA_TYPE, copy: bool = True, **opts) -> Cast:
7187    """Cast an expression to a data type.
7188
7189    Example:
7190        >>> cast('x + 1', 'int').sql()
7191        'CAST(x + 1 AS INT)'
7192
7193    Args:
7194        expression: The expression to cast.
7195        to: The datatype to cast to.
7196        copy: Whether to copy the supplied expressions.
7197
7198    Returns:
7199        The new Cast instance.
7200    """
7201    expr = maybe_parse(expression, copy=copy, **opts)
7202    data_type = DataType.build(to, copy=copy, **opts)
7203
7204    if expr.is_type(data_type):
7205        return expr
7206
7207    expr = Cast(this=expr, to=data_type)
7208    expr.type = data_type
7209
7210    return expr
7211
7212
7213def table_(
7214    table: Identifier | str,
7215    db: t.Optional[Identifier | str] = None,
7216    catalog: t.Optional[Identifier | str] = None,
7217    quoted: t.Optional[bool] = None,
7218    alias: t.Optional[Identifier | str] = None,
7219) -> Table:
7220    """Build a Table.
7221
7222    Args:
7223        table: Table name.
7224        db: Database name.
7225        catalog: Catalog name.
7226        quote: Whether to force quotes on the table's identifiers.
7227        alias: Table's alias.
7228
7229    Returns:
7230        The new Table instance.
7231    """
7232    return Table(
7233        this=to_identifier(table, quoted=quoted) if table else None,
7234        db=to_identifier(db, quoted=quoted) if db else None,
7235        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
7236        alias=TableAlias(this=to_identifier(alias)) if alias else None,
7237    )
7238
7239
7240def values(
7241    values: t.Iterable[t.Tuple[t.Any, ...]],
7242    alias: t.Optional[str] = None,
7243    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
7244) -> Values:
7245    """Build VALUES statement.
7246
7247    Example:
7248        >>> values([(1, '2')]).sql()
7249        "VALUES (1, '2')"
7250
7251    Args:
7252        values: values statements that will be converted to SQL
7253        alias: optional alias
7254        columns: Optional list of ordered column names or ordered dictionary of column names to types.
7255         If either are provided then an alias is also required.
7256
7257    Returns:
7258        Values: the Values expression object
7259    """
7260    if columns and not alias:
7261        raise ValueError("Alias is required when providing columns")
7262
7263    return Values(
7264        expressions=[convert(tup) for tup in values],
7265        alias=(
7266            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
7267            if columns
7268            else (TableAlias(this=to_identifier(alias)) if alias else None)
7269        ),
7270    )
7271
7272
7273def var(name: t.Optional[ExpOrStr]) -> Var:
7274    """Build a SQL variable.
7275
7276    Example:
7277        >>> repr(var('x'))
7278        'Var(this=x)'
7279
7280        >>> repr(var(column('x', table='y')))
7281        'Var(this=x)'
7282
7283    Args:
7284        name: The name of the var or an expression who's name will become the var.
7285
7286    Returns:
7287        The new variable node.
7288    """
7289    if not name:
7290        raise ValueError("Cannot convert empty name into var.")
7291
7292    if isinstance(name, Expression):
7293        name = name.name
7294    return Var(this=name)
7295
7296
7297def rename_table(
7298    old_name: str | Table,
7299    new_name: str | Table,
7300    dialect: DialectType = None,
7301) -> Alter:
7302    """Build ALTER TABLE... RENAME... expression
7303
7304    Args:
7305        old_name: The old name of the table
7306        new_name: The new name of the table
7307        dialect: The dialect to parse the table.
7308
7309    Returns:
7310        Alter table expression
7311    """
7312    old_table = to_table(old_name, dialect=dialect)
7313    new_table = to_table(new_name, dialect=dialect)
7314    return Alter(
7315        this=old_table,
7316        kind="TABLE",
7317        actions=[
7318            RenameTable(this=new_table),
7319        ],
7320    )
7321
7322
7323def rename_column(
7324    table_name: str | Table,
7325    old_column_name: str | Column,
7326    new_column_name: str | Column,
7327    exists: t.Optional[bool] = None,
7328    dialect: DialectType = None,
7329) -> Alter:
7330    """Build ALTER TABLE... RENAME COLUMN... expression
7331
7332    Args:
7333        table_name: Name of the table
7334        old_column: The old name of the column
7335        new_column: The new name of the column
7336        exists: Whether to add the `IF EXISTS` clause
7337        dialect: The dialect to parse the table/column.
7338
7339    Returns:
7340        Alter table expression
7341    """
7342    table = to_table(table_name, dialect=dialect)
7343    old_column = to_column(old_column_name, dialect=dialect)
7344    new_column = to_column(new_column_name, dialect=dialect)
7345    return Alter(
7346        this=table,
7347        kind="TABLE",
7348        actions=[
7349            RenameColumn(this=old_column, to=new_column, exists=exists),
7350        ],
7351    )
7352
7353
7354def convert(value: t.Any, copy: bool = False) -> Expression:
7355    """Convert a python value into an expression object.
7356
7357    Raises an error if a conversion is not possible.
7358
7359    Args:
7360        value: A python object.
7361        copy: Whether to copy `value` (only applies to Expressions and collections).
7362
7363    Returns:
7364        The equivalent expression object.
7365    """
7366    if isinstance(value, Expression):
7367        return maybe_copy(value, copy)
7368    if isinstance(value, str):
7369        return Literal.string(value)
7370    if isinstance(value, bool):
7371        return Boolean(this=value)
7372    if value is None or (isinstance(value, float) and math.isnan(value)):
7373        return null()
7374    if isinstance(value, numbers.Number):
7375        return Literal.number(value)
7376    if isinstance(value, bytes):
7377        return HexString(this=value.hex())
7378    if isinstance(value, datetime.datetime):
7379        datetime_literal = Literal.string(
7380            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat(
7381                sep=" "
7382            )
7383        )
7384        return TimeStrToTime(this=datetime_literal)
7385    if isinstance(value, datetime.date):
7386        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
7387        return DateStrToDate(this=date_literal)
7388    if isinstance(value, tuple):
7389        if hasattr(value, "_fields"):
7390            return Struct(
7391                expressions=[
7392                    PropertyEQ(
7393                        this=to_identifier(k), expression=convert(getattr(value, k), copy=copy)
7394                    )
7395                    for k in value._fields
7396                ]
7397            )
7398        return Tuple(expressions=[convert(v, copy=copy) for v in value])
7399    if isinstance(value, list):
7400        return Array(expressions=[convert(v, copy=copy) for v in value])
7401    if isinstance(value, dict):
7402        return Map(
7403            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
7404            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
7405        )
7406    if hasattr(value, "__dict__"):
7407        return Struct(
7408            expressions=[
7409                PropertyEQ(this=to_identifier(k), expression=convert(v, copy=copy))
7410                for k, v in value.__dict__.items()
7411            ]
7412        )
7413    raise ValueError(f"Cannot convert {value}")
7414
7415
7416def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
7417    """
7418    Replace children of an expression with the result of a lambda fun(child) -> exp.
7419    """
7420    for k, v in tuple(expression.args.items()):
7421        is_list_arg = type(v) is list
7422
7423        child_nodes = v if is_list_arg else [v]
7424        new_child_nodes = []
7425
7426        for cn in child_nodes:
7427            if isinstance(cn, Expression):
7428                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
7429                    new_child_nodes.append(child_node)
7430            else:
7431                new_child_nodes.append(cn)
7432
7433        expression.set(k, new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0))
7434
7435
7436def replace_tree(
7437    expression: Expression,
7438    fun: t.Callable,
7439    prune: t.Optional[t.Callable[[Expression], bool]] = None,
7440) -> Expression:
7441    """
7442    Replace an entire tree with the result of function calls on each node.
7443
7444    This will be traversed in reverse dfs, so leaves first.
7445    If new nodes are created as a result of function calls, they will also be traversed.
7446    """
7447    stack = list(expression.dfs(prune=prune))
7448
7449    while stack:
7450        node = stack.pop()
7451        new_node = fun(node)
7452
7453        if new_node is not node:
7454            node.replace(new_node)
7455
7456            if isinstance(new_node, Expression):
7457                stack.append(new_node)
7458
7459    return new_node
7460
7461
7462def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
7463    """
7464    Return all table names referenced through columns in an expression.
7465
7466    Example:
7467        >>> import sqlglot
7468        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
7469        ['a', 'c']
7470
7471    Args:
7472        expression: expression to find table names.
7473        exclude: a table name to exclude
7474
7475    Returns:
7476        A list of unique names.
7477    """
7478    return {
7479        table
7480        for table in (column.table for column in expression.find_all(Column))
7481        if table and table != exclude
7482    }
7483
7484
7485def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
7486    """Get the full name of a table as a string.
7487
7488    Args:
7489        table: Table expression node or string.
7490        dialect: The dialect to generate the table name for.
7491        identify: Determines when an identifier should be quoted. Possible values are:
7492            False (default): Never quote, except in cases where it's mandatory by the dialect.
7493            True: Always quote.
7494
7495    Examples:
7496        >>> from sqlglot import exp, parse_one
7497        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
7498        'a.b.c'
7499
7500    Returns:
7501        The table name.
7502    """
7503
7504    table = maybe_parse(table, into=Table, dialect=dialect)
7505
7506    if not table:
7507        raise ValueError(f"Cannot parse {table}")
7508
7509    return ".".join(
7510        (
7511            part.sql(dialect=dialect, identify=True, copy=False)
7512            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
7513            else part.name
7514        )
7515        for part in table.parts
7516    )
7517
7518
7519def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
7520    """Returns a case normalized table name without quotes.
7521
7522    Args:
7523        table: the table to normalize
7524        dialect: the dialect to use for normalization rules
7525        copy: whether to copy the expression.
7526
7527    Examples:
7528        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
7529        'A-B.c'
7530    """
7531    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
7532
7533    return ".".join(
7534        p.name
7535        for p in normalize_identifiers(
7536            to_table(table, dialect=dialect, copy=copy), dialect=dialect
7537        ).parts
7538    )
7539
7540
7541def replace_tables(
7542    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
7543) -> E:
7544    """Replace all tables in expression according to the mapping.
7545
7546    Args:
7547        expression: expression node to be transformed and replaced.
7548        mapping: mapping of table names.
7549        dialect: the dialect of the mapping table
7550        copy: whether to copy the expression.
7551
7552    Examples:
7553        >>> from sqlglot import exp, parse_one
7554        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
7555        'SELECT * FROM c /* a.b */'
7556
7557    Returns:
7558        The mapped expression.
7559    """
7560
7561    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
7562
7563    def _replace_tables(node: Expression) -> Expression:
7564        if isinstance(node, Table):
7565            original = normalize_table_name(node, dialect=dialect)
7566            new_name = mapping.get(original)
7567
7568            if new_name:
7569                table = to_table(
7570                    new_name,
7571                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
7572                    dialect=dialect,
7573                )
7574                table.add_comments([original])
7575                return table
7576        return node
7577
7578    return expression.transform(_replace_tables, copy=copy)  # type: ignore
7579
7580
7581def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
7582    """Replace placeholders in an expression.
7583
7584    Args:
7585        expression: expression node to be transformed and replaced.
7586        args: positional names that will substitute unnamed placeholders in the given order.
7587        kwargs: keyword arguments that will substitute named placeholders.
7588
7589    Examples:
7590        >>> from sqlglot import exp, parse_one
7591        >>> replace_placeholders(
7592        ...     parse_one("select * from :tbl where ? = ?"),
7593        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
7594        ... ).sql()
7595        "SELECT * FROM foo WHERE str_col = 'b'"
7596
7597    Returns:
7598        The mapped expression.
7599    """
7600
7601    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
7602        if isinstance(node, Placeholder):
7603            if node.this:
7604                new_name = kwargs.get(node.this)
7605                if new_name is not None:
7606                    return convert(new_name)
7607            else:
7608                try:
7609                    return convert(next(args))
7610                except StopIteration:
7611                    pass
7612        return node
7613
7614    return expression.transform(_replace_placeholders, iter(args), **kwargs)
7615
7616
7617def expand(
7618    expression: Expression,
7619    sources: t.Dict[str, Query],
7620    dialect: DialectType = None,
7621    copy: bool = True,
7622) -> Expression:
7623    """Transforms an expression by expanding all referenced sources into subqueries.
7624
7625    Examples:
7626        >>> from sqlglot import parse_one
7627        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
7628        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
7629
7630        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
7631        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
7632
7633    Args:
7634        expression: The expression to expand.
7635        sources: A dictionary of name to Queries.
7636        dialect: The dialect of the sources dict.
7637        copy: Whether to copy the expression during transformation. Defaults to True.
7638
7639    Returns:
7640        The transformed expression.
7641    """
7642    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
7643
7644    def _expand(node: Expression):
7645        if isinstance(node, Table):
7646            name = normalize_table_name(node, dialect=dialect)
7647            source = sources.get(name)
7648            if source:
7649                subquery = source.subquery(node.alias or name)
7650                subquery.comments = [f"source: {name}"]
7651                return subquery.transform(_expand, copy=False)
7652        return node
7653
7654    return expression.transform(_expand, copy=copy)
7655
7656
7657def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
7658    """
7659    Returns a Func expression.
7660
7661    Examples:
7662        >>> func("abs", 5).sql()
7663        'ABS(5)'
7664
7665        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
7666        'CAST(5 AS DOUBLE)'
7667
7668    Args:
7669        name: the name of the function to build.
7670        args: the args used to instantiate the function of interest.
7671        copy: whether to copy the argument expressions.
7672        dialect: the source dialect.
7673        kwargs: the kwargs used to instantiate the function of interest.
7674
7675    Note:
7676        The arguments `args` and `kwargs` are mutually exclusive.
7677
7678    Returns:
7679        An instance of the function of interest, or an anonymous function, if `name` doesn't
7680        correspond to an existing `sqlglot.expressions.Func` class.
7681    """
7682    if args and kwargs:
7683        raise ValueError("Can't use both args and kwargs to instantiate a function.")
7684
7685    from sqlglot.dialects.dialect import Dialect
7686
7687    dialect = Dialect.get_or_raise(dialect)
7688
7689    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
7690    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
7691
7692    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
7693    if constructor:
7694        if converted:
7695            if "dialect" in constructor.__code__.co_varnames:
7696                function = constructor(converted, dialect=dialect)
7697            else:
7698                function = constructor(converted)
7699        elif constructor.__name__ == "from_arg_list":
7700            function = constructor.__self__(**kwargs)  # type: ignore
7701        else:
7702            constructor = FUNCTION_BY_NAME.get(name.upper())
7703            if constructor:
7704                function = constructor(**kwargs)
7705            else:
7706                raise ValueError(
7707                    f"Unable to convert '{name}' into a Func. Either manually construct "
7708                    "the Func expression of interest or parse the function call."
7709                )
7710    else:
7711        kwargs = kwargs or {"expressions": converted}
7712        function = Anonymous(this=name, **kwargs)
7713
7714    for error_message in function.error_messages(converted):
7715        raise ValueError(error_message)
7716
7717    return function
7718
7719
7720def case(
7721    expression: t.Optional[ExpOrStr] = None,
7722    **opts,
7723) -> Case:
7724    """
7725    Initialize a CASE statement.
7726
7727    Example:
7728        case().when("a = 1", "foo").else_("bar")
7729
7730    Args:
7731        expression: Optionally, the input expression (not all dialects support this)
7732        **opts: Extra keyword arguments for parsing `expression`
7733    """
7734    if expression is not None:
7735        this = maybe_parse(expression, **opts)
7736    else:
7737        this = None
7738    return Case(this=this, ifs=[])
7739
7740
7741def array(
7742    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7743) -> Array:
7744    """
7745    Returns an array.
7746
7747    Examples:
7748        >>> array(1, 'x').sql()
7749        'ARRAY(1, x)'
7750
7751    Args:
7752        expressions: the expressions to add to the array.
7753        copy: whether to copy the argument expressions.
7754        dialect: the source dialect.
7755        kwargs: the kwargs used to instantiate the function of interest.
7756
7757    Returns:
7758        An array expression.
7759    """
7760    return Array(
7761        expressions=[
7762            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7763            for expression in expressions
7764        ]
7765    )
7766
7767
7768def tuple_(
7769    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
7770) -> Tuple:
7771    """
7772    Returns an tuple.
7773
7774    Examples:
7775        >>> tuple_(1, 'x').sql()
7776        '(1, x)'
7777
7778    Args:
7779        expressions: the expressions to add to the tuple.
7780        copy: whether to copy the argument expressions.
7781        dialect: the source dialect.
7782        kwargs: the kwargs used to instantiate the function of interest.
7783
7784    Returns:
7785        A tuple expression.
7786    """
7787    return Tuple(
7788        expressions=[
7789            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
7790            for expression in expressions
7791        ]
7792    )
7793
7794
7795def true() -> Boolean:
7796    """
7797    Returns a true Boolean expression.
7798    """
7799    return Boolean(this=True)
7800
7801
7802def false() -> Boolean:
7803    """
7804    Returns a false Boolean expression.
7805    """
7806    return Boolean(this=False)
7807
7808
7809def null() -> Null:
7810    """
7811    Returns a Null expression.
7812    """
7813    return Null()
7814
7815
7816NONNULL_CONSTANTS = (
7817    Literal,
7818    Boolean,
7819)
7820
7821CONSTANTS = (
7822    Literal,
7823    Boolean,
7824    Null,
7825)